Re: [PHP-DB] SUM and JOIN together
Unless you are using 4.1 (which allows subqueries), you should perform 3 queries, the first 2 creating temporary tables: - sum - difference - left join Ignatius _ - Original Message - From: "rogue" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, February 19, 2004 04:24 Subject: [PHP-DB] SUM and JOIN together > Hi all, > > Not sure how to handle this situation and was hoping someone with a > bigger brain then me could help. > > 2 tables > > tbl_hoursbought > > id | client | hours > > 1 | test | 5 > 2 | test | 10 > > > tbl_hoursused > > > id | client | hours > > 1 | test | 3 > > > What I am trying to do is sum the hours bought and the hours used, then > subtract the values to get the available hours. The problem I am having > is that I get a multiple (based on how many rows are in the first > table) of the value in the second table. Here is my query: > > select sum(tbl_hoursbought.hours) as bought, sum(tbl_hoursused.hours) > as used > from tbl_hoursbought > left join tbl_hoursused > on tbl_hoursbought.client = tbl_hoursused.client > and tbl_hoursbought.client = $id > > > So my results return 15 for bought (which is correct) but I get 6 for > used since there are 2 rows in bought (I think). I figure I could just > divide that by the number of results, but is there a 'correct' way of > doing it in the SQL statement? > > Thanks for any help. > Please copy me with any replies as I am on the digest. > > thanks, > rogue > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SUM and JOIN together
Hi all, Not sure how to handle this situation and was hoping someone with a bigger brain then me could help. 2 tables tbl_hoursbought id | client | hours 1 | test | 5 2 | test | 10 tbl_hoursused id | client | hours 1 | test | 3 What I am trying to do is sum the hours bought and the hours used, then subtract the values to get the available hours. The problem I am having is that I get a multiple (based on how many rows are in the first table) of the value in the second table. Here is my query: select sum(tbl_hoursbought.hours) as bought, sum(tbl_hoursused.hours) as used from tbl_hoursbought left join tbl_hoursused on tbl_hoursbought.client = tbl_hoursused.client and tbl_hoursbought.client = $id So my results return 15 for bought (which is correct) but I get 6 for used since there are 2 rows in bought (I think). I figure I could just divide that by the number of results, but is there a 'correct' way of doing it in the SQL statement? Thanks for any help. Please copy me with any replies as I am on the digest. thanks, rogue -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Statement
hi,
thank you for your reply but I can't do a multiple table update because my
server is using MySQL with version lesser than 4.0... please enlighten me
how I can write the same update statement without using multiple table
update.. is there a way to use JOIN or something??
thanks thanks..
hwee
- Original Message -
From: "Ricardo Lopes" <[EMAIL PROTECTED]>
To: "Ng Hwee Hwee" <[EMAIL PROTECTED]>
Cc: "PHP DB" <[EMAIL PROTECTED]>
Sent: Wednesday, February 18, 2004 5:41 PM
Subject: Re: [PHP-DB] Update Statement
> you could use:
>
> for ($i=0; $i< max; $i++)
> {
> mysql_query("update Table1, Table2 set Table1.field1 = Table2.field2
> where Table1.no = Table2.no");
> }
>
>
> OR if you use mysqlt daemon (support for transactions, in this case is
best)
>
> mysql_query('BEGIN');
> for ($i=0; $i< max; $i++)
> {
>if (! mysql_query("update Table1, Table2 set Table1.field1 =
> Table2.field2 where Table1.no = Table2.no"))
> {
> mysql_query('ROLLBACK');
> die('ERROR - in query nº: '.$i);
> }
> }
> mysql_query(COMMIT');
>
>
> Execute all or not execute none. I personally use adodb to connect to
> databases and control transactions.
>
> If what you really want is to execute then all in just one function call
to
> the database, i don't know how can you do it with your mysql version.
>
> HTH
>
> - Original Message -
> From: "Ng Hwee Hwee" <[EMAIL PROTECTED]>
> To: "DBList" <[EMAIL PROTECTED]>
> Sent: Wednesday, February 18, 2004 2:14 AM
> Subject: [PHP-DB] Update Statement
>
>
> hi all,
>
> I would like to do the following:
>
> update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no =
> Table2.no
>
> however, i found that multiple updates can only work for MySQL version
4.0.4
> but mine is 3.23.54... can anyone enlighten me on how I can achieve the
same
> results with this older MySQL version? or can PHP 4.2.2 help me in any
way?
>
> Thank you so much!
>
> Hwee
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: oracle: fetch a query result into an array
To make your life much easier, I recommend using PEAR DB. The function
calls are very easy and work for any DBMS (no remembering DB specific
calls :-)). It also gives you lots of added functionality, such as
fetching of an entire result set at once, fetching only one row, or even
fetching one column quickly and without the need to worry about freeing
your resources. Used right, you don't have to worry about memory leaks
or even the DB backend you're using.
http://pear.php.net/package/DB
http://pear.php.net/manual/en/package.database.php
Torsten Lange wrote:
Hello,
I'm a php beginner and not succeed in fetching an oracle query result
into an array. I want to load the colum names of a table (from an
Oracle9i) into a -field. There is something working, but only
the last column name appears in the select field and is wraped by each
letter.
For instense acw_fid_lab:
___
|non|
|all|
|A |
|C |
.
. and so on.
What are my mistakes? If anyone could suggest me how to solve it or
using a better approach...
Thank you, Torsten
---
My last version (I also tried OCIFetchInto before):
---
function fill_select($table_name)
{
$connect = @OCILogon("", "", "");
if(!$connect)
{
$err_oci = OCIError();
echo "(2) No connection - OCIError():
".$err_oci["message"];
echo "";
}
else
{
$sql_table_names = "SELECT column_name FROM user_tab_columns
WHERE table_name = '".$table_name."'";
$stmt = OCIParse($connect, $sql_table_names);
OCIExecute($stmt);
$column_name = array();
$counter = 0;
while(OCIFetch($stmt))
{
$column_name = OCIResult($stmt,'COLUMN_NAME');
$counter++;
}
if($counter == 0)
{
$column_name[0] = "nothing found";
}
OCIFreeStatement($stmt);
OCILogOff($connect);
return($column_name);
}
}
# This is a cut-out from that file I call the function from
echo " \n";
echo " \n";
echo " none\n";
echo " all\n";
$table_name = "AN_CHEMISTRY_WATER";
$count = (int) count_table_columns($table_name);
$column_names = fill_select($table_name);
for($i=1; $i<=$count; $i++)
{
echo "",$column_names[$i],"\n";
}
echo " \n";
echo " \n";
--
--
paperCrane
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Interbase: How to get table structure via SQL ?
Dizem Pgc wrote: Hi, ...table structure without PEAR ? Is it possible to accomplish this by a sql-statement or do I really HAVE TO install this xtension (PEAR) ? Cuz I've already written a few scripts containing many function calls (self written classes, too). I read the PEAR extension organizes everything with packages. So, if I install PEAR, do I have to change all my function calls? I'd like to find a more 'simple' solution for my problem. (P.S.: Thx to R.Hutchins 4 answering quickly!) Bye, DiZEM No you don't have to install PEAR. It just uses SQL to do it all anyway. This SQL works in other DBs (not sure about Interbase): DESCRIBE table And no, you don't have to convert all of your old code if you do install PEAR packagesthat is, as long as your names don't clash. I highly recommend at least looking at PEAR's DB. Or even DB_DataObject. It gives a lot of (highly tested) high level abstraction and easy to use functions. It's quick to use and shouldn't slow down your scripts any (especially if you did convert everything to use PEAR). Plus you get free updates without having to do all of the debugging and such on your own. Add to that standardization and all of the other packages you can use. :-) -- paperCrane -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] run some tests on mysqli, please
Hi Folks: Does anyone here have the mysqli extension running correctly on their machines? If so, could you please help the PEAR DB project by running the test suite? Instructions for doing so are here: http://cvs.php.net/co.php/pear/DB/doc/TESTERS Pardon me for posting to a list I'm not on, but it seemed important. Please email me off list with the results. Thanks, --Dan -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] [Attn] Re: [PHP-DB] SQLite behaving bad?
On Tuesday 17 February 2004 04:41 pm, Gerard Samuel wrote:
> Currently using sqlite 2.8.11 (distributed by php snaps) with php4.3.5rc2
> on windows XP.
> Several months ago, I added support for sqlite in a database abstraction
> class I created. It worked great then.
> Im trying to see if things are still ok today, and its not.
> For example executing the select query ->
> select f.foo, b.bar from table f, table b where f.id = b.id;
> Should return result table fields "foo" and "bar".
> Instead its returning result table fields "f.foo" and "b.bar".
>
Just an update about this.
Due to changes in SQLite, the problem that I've described in this email, is
now the default behaviour of SQLite.
So if you get bitten by this, its up to you to figure a way around this.
> Example script
>
> // sqlite connection parameters produce $conn resource id
>
> $sql = 'SELECT f.foo, b.bar FROM table f, table b WHERE f.id = b.id';
> $result = sqlite_query($conn, $sql);
> if ($result === false)
> {
>die('For some reason');
> }
>
> while($row = sqlite_fetch_array($result, SQLITE_ASSOC))
> {
> // The expected results return nothing
> var_dump($row['foo'], $row['bar']);
>
> // The unexpected results return data
> var_dump($row['f.foo'], $row['b.bar']); // You get the correct values
> }
>
> ?>
>
> So has anyone experienced this with the current sqlite/php combination?
> This used to work correctly with sqlite 2.8.3 (distributed by php snaps)
> Were there any notices that I may have missed over these past months??
>
> Thanks for any help you may provide...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] oracle: fetch a query result into an array
On 18 February 2004 18:22, Torsten Lange contributed these pearls of wisdom:
>
> while(OCIFetch($stmt))
> {
> $column_name = OCIResult($stmt,'COLUMN_NAME');
missing [] here -- should be:
$column_name[] = OCIResult($stmt,'COLUMN_NAME');
> $counter++; }
You might also want to look up ocifetchstatement
(http://www.php.net/ocifetchstatement).
Cheers!
Mike
--
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Beckett Park, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] oracle: fetch a query result into an array
Hello,
I'm a php beginner and not succeed in fetching an oracle query result
into an array. I want to load the colum names of a table (from an
Oracle9i) into a -field. There is something working, but only
the last column name appears in the select field and is wraped by each
letter.
For instense acw_fid_lab:
___
|non|
|all|
|A |
|C |
.
. and so on.
What are my mistakes? If anyone could suggest me how to solve it or
using a better approach...
Thank you, Torsten
---
My last version (I also tried OCIFetchInto before):
---
function fill_select($table_name)
{
$connect = @OCILogon("", "", "");
if(!$connect)
{
$err_oci = OCIError();
echo "(2) No connection - OCIError():
".$err_oci["message"];
echo "";
}
else
{
$sql_table_names = "SELECT column_name FROM user_tab_columns
WHERE table_name = '".$table_name."'";
$stmt = OCIParse($connect, $sql_table_names);
OCIExecute($stmt);
$column_name = array();
$counter = 0;
while(OCIFetch($stmt))
{
$column_name = OCIResult($stmt,'COLUMN_NAME');
$counter++;
}
if($counter == 0)
{
$column_name[0] = "nothing found";
}
OCIFreeStatement($stmt);
OCILogOff($connect);
return($column_name);
}
}
# This is a cut-out from that file I call the function from
echo " \n";
echo " \n";
echo " none\n";
echo " all\n";
$table_name = "AN_CHEMISTRY_WATER";
$count = (int) count_table_columns($table_name);
$column_names = fill_select($table_name);
for($i=1; $i<=$count; $i++)
{
echo "",$column_names[$i],"\n";
}
echo " \n";
echo " \n";
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Is it safe to do this?
Jerry wrote: That depends on what you're doing. Here's why I can't provide feedback: 1) Your code isn't at all complete, the foreach isn't even finished. Not what matters here, I just snipped a bit of code in here. I'm not even concerned about it because the code works fine. I just need some work on a query. 2) Where does the $addresses variable come from and what are you trying todo with it? Again it's a snipped code. $addresses collects information from an emails. It searches for special characters only, if no matches it does nothing, if a match it runs a query. So looks for matches such as @, +, _ for email addresses in the original email sent, minus the headers. 3) What is the mysql table and its columns supposed to represent? What is the mysql table? I'm not sure what your asking here. What's it for? For my site. Why do you need me to explain that? I think my query explains what's going on here. Another person responded to me about this and new exactly what I was trying to do. But for the record it's for a bouncing email script. To handle bounced emails, again it's only a bit of snipped code, the rest is fine. 4) What is this code supposed to accomplish? Bounced emails, if an email is bounced it will do a search and do something if and flag account if it's bad. If it finds an email that is not in the DB it also does nothing. (spam emails mainly go into that account) As for the query. I want to change a value of a field when it reaches a certian value. So was wondering if it was safe to change a value in one field once it reaches a set value. Okay with my program site emails that have a email_setting of: 0 - All emails go to external inboxes and internal inboxes 1 - All emails go to external inboxes and not internal 2 - All emails go to internal inboxes and not external Each user can toggle what they want, ext. is real email addresses... int. is the internal one with the account which is not an email just an inbox. So when it reaches a value of -5 means it's bounced 5 times therefore needs to be set to a lower value so emails are not sent. So I have: field1 field2 field1 - is used for 0 , 1 , 2 value to send emails to inboxes internal and external. Anything set in negative figures no internal or external email will be sent to that user. field2 - is used to track the number of times the email has bounced. So when it reaches 5 I need to handle it and reset filed1 to a negative value. So regarding my first example, it's no longer used by me and won't go back to it. But is example 2 going to work? ... UPDATE users SET email_setting='-1' WHERE emailerror > 5 ... will that set email_setting to -1 if emailerror has a value of 5 or more? Yes, it will. -- paperCrane -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Interbase: How to get table structure via SQL ?
Hi, ...table structure without PEAR ? Is it possible to accomplish this by a sql-statement or do I really HAVE TO install this xtension (PEAR) ? Cuz I've already written a few scripts containing many function calls (self written classes, too). I read the PEAR extension organizes everything with packages. So, if I install PEAR, do I have to change all my function calls? I'd like to find a more 'simple' solution for my problem. (P.S.: Thx to R.Hutchins 4 answering quickly!) Bye, DiZEM -- GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 EUR/Monat...) jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: php/mysql weblog - addnews function problems
I've tried playing around with it a bit but I'm getting quite confused now.
It still doesn't like my declaration of variables and I'm not sure how to
declare 'id' either as it isn't used in the form as a field of entry - just
an index in the database.
Any help would greatly be appreciated cause I'm using this as an inclusion
in a piece of coursework I'm doing and the deadline is looming! :/
thanks
slippy
"Doug Thompson" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Wed, 18 Feb 2004 09:35:22 -, Slippyaah wrote:
>
> >Well basically I think it's a problem with the declaration of variables
> >and/or the way they are being processed by the form.
> >
> >Here is the individual addNews function:
> >
> >function addNews() {
> > global $db;
> >
> > /* declare variables here? */
> >
> >
> >
> > /* insert the new news entry */
> > $query = "INSERT INTO news" .
> > "VALUES('',$id,'{$_POST['postdate']}'," .
> > "'{$_POST['title']}',".
> > "'{$_POST['newstext']}')";
>
> I haven't been following this thread, but you need to do your part to help
the parser.
>
> $query = "INSERT INTO news ";
> $query .= "VALUES('',$id,'".$_POST['postdate']."',";
> $query .= "'".$_POST['title']."',";
> $query .= "'".$_POST['newstext']."')";
>
> You should also add some db error checking and reporting, then you
wouldn't get the erroneous "success" message.
>
> hth,
> Doug
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Search results of a previous query?
You can concat to the 1º filter statement the 2º. ex: SELECT * FROM atable WHERE (fieldname = fieldvalue) AND (fieldname2 = fieldvalue2) For the user to search twice in the same field i think you have to use the LIKE operator instead of the = and use the % in the string as wildcard. ex: 1º Search: Name = "%Carlos%"// Will return all Carlos 2º Search: Name = "%Triana%"// Will return everybody that have Carlos and Triana in any part of their name if used a sql like the one above (with AND). This could be done how many times you want with only one field or several. Hope this Hepls. - Original Message - From: "Lic. Carlos A. Triana Torres" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 18, 2004 1:36 PM Subject: [PHP-DB] Search results of a previous query? Hi all, I am trying to build a script to make "nested" searches in a table. I have a form compossed of a text field and a select dropdown menu with all the fields in the table. The user can write in the text box and then select the field in which to perform the search. This works fine so far...now, I want to show the results of the query, but give the user the possibility of searching again BUT only make the search with the results shown, not the entire tablecan anyone give me any ideas of how to do this last part? I tried passing the new queries in the url...but this won't give the user the possibility to search twice in the same field... Any ideas? Thanks before hand. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Interbase: How to get table structure via SQL ?
Us use the PEAR db abstraction layer which has a function to do this pete Dizem Pgc wrote: Hi, does anyone know how to get the Structure of a table using SQL. (ex.: SELECT ?RELATION_SOURCE?, RDB$RELATION_NAME FROM RDB$RELATIONS) Isn't there any table containing the CREATE STATEMENT for a table? Maybe I have to use another command? Please help ! Thx, DiZEM -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Search results of a previous query?
Hi all, I am trying to build a script to make "nested" searches in a table. I have a form compossed of a text field and a select dropdown menu with all the fields in the table. The user can write in the text box and then select the field in which to perform the search. This works fine so far...now, I want to show the results of the query, but give the user the possibility of searching again BUT only make the search with the results shown, not the entire tablecan anyone give me any ideas of how to do this last part? I tried passing the new queries in the url...but this won't give the user the possibility to search twice in the same field... Any ideas? Thanks before hand.
RE: [PHP-DB] Interbase: How to get table structure via SQL ?
Sorry...just re-read the subject and realized you're using Interbase. Need coffee. ;) > -Original Message- > From: Hutchins, Richard [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 18, 2004 8:24 AM > To: 'DiZEM PGC'; [EMAIL PROTECTED] > Subject: RE: [PHP-DB] Interbase: How to get table structure via SQL ? > > > If you're using MySQL, you can use the DESCRIBE tablename > command. There's a > pretty straightforward (and easy to find) section on the > DESCRIBE command in > the MySQL documentation. > > > > -Original Message- > > From: DiZEM PGC [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, February 18, 2004 8:11 AM > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] Interbase: How to get table structure via SQL ? > > > > > > Hi, > > > > does anyone know how to get the Structure of a table using SQL. > > (ex.: SELECT ?RELATION_SOURCE?, RDB$RELATION_NAME FROM > RDB$RELATIONS) > > > > Isn't there any table containing the CREATE STATEMENT for a table? > > Maybe I have to use another command? > > Please help ! > > > > Thx, > > DiZEM > > > > -- > > GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 > > EUR/Monat...) > > jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ > http://www.gmx.net/derspiegel +++ > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Interbase: How to get table structure via SQL ?
If you're using MySQL, you can use the DESCRIBE tablename command. There's a pretty straightforward (and easy to find) section on the DESCRIBE command in the MySQL documentation. > -Original Message- > From: DiZEM PGC [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 18, 2004 8:11 AM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] Interbase: How to get table structure via SQL ? > > > Hi, > > does anyone know how to get the Structure of a table using SQL. > (ex.: SELECT ?RELATION_SOURCE?, RDB$RELATION_NAME FROM RDB$RELATIONS) > > Isn't there any table containing the CREATE STATEMENT for a table? > Maybe I have to use another command? > Please help ! > > Thx, > DiZEM > > -- > GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 > EUR/Monat...) > jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Interbase: How to get table structure via SQL ?
Hi, does anyone know how to get the Structure of a table using SQL. (ex.: SELECT ?RELATION_SOURCE?, RDB$RELATION_NAME FROM RDB$RELATIONS) Isn't there any table containing the CREATE STATEMENT for a table? Maybe I have to use another command? Please help ! Thx, DiZEM -- GMX ProMail (250 MB Mailbox, 50 FreeSMS, Virenschutz, 2,99 EUR/Monat...) jetzt 3 Monate GRATIS + 3x DER SPIEGEL +++ http://www.gmx.net/derspiegel +++ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: php/mysql weblog - addnews function problems
On Wed, 18 Feb 2004 09:35:22 -, Slippyaah wrote:
>Well basically I think it's a problem with the declaration of variables
>and/or the way they are being processed by the form.
>
>Here is the individual addNews function:
>
>function addNews() {
> global $db;
>
> /* declare variables here? */
>
>
>
> /* insert the new news entry */
> $query = "INSERT INTO news" .
> "VALUES('',$id,'{$_POST['postdate']}'," .
> "'{$_POST['title']}',".
> "'{$_POST['newstext']}')";
I haven't been following this thread, but you need to do your part to help the parser.
$query = "INSERT INTO news ";
$query .= "VALUES('',$id,'".$_POST['postdate']."',";
$query .= "'".$_POST['title']."',";
$query .= "'".$_POST['newstext']."')";
You should also add some db error checking and reporting, then you wouldn't get the
erroneous "success" message.
hth,
Doug
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Update Statement
This one cooked me 2 when I realised my online by was 3x and local was 4x only 4+ supports a join on an update statement from the manual Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; pete Ng Hwee Hwee wrote: hi all, I would like to do the following: update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no however, i found that multiple updates can only work for MySQL version 4.0.4 but mine is 3.23.54... can anyone enlighten me on how I can achieve the same results with this older MySQL version? or can PHP 4.2.2 help me in any way? Thank you so much! Hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Statement
you could use:
for ($i=0; $i< max; $i++)
{
mysql_query("update Table1, Table2 set Table1.field1 = Table2.field2
where Table1.no = Table2.no");
}
OR if you use mysqlt daemon (support for transactions, in this case is best)
mysql_query('BEGIN');
for ($i=0; $i< max; $i++)
{
if (! mysql_query("update Table1, Table2 set Table1.field1 =
Table2.field2 where Table1.no = Table2.no"))
{
mysql_query('ROLLBACK');
die('ERROR - in query nº: '.$i);
}
}
mysql_query(COMMIT');
Execute all or not execute none. I personally use adodb to connect to
databases and control transactions.
If what you really want is to execute then all in just one function call to
the database, i don't know how can you do it with your mysql version.
HTH
- Original Message -
From: "Ng Hwee Hwee" <[EMAIL PROTECTED]>
To: "DBList" <[EMAIL PROTECTED]>
Sent: Wednesday, February 18, 2004 2:14 AM
Subject: [PHP-DB] Update Statement
hi all,
I would like to do the following:
update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no =
Table2.no
however, i found that multiple updates can only work for MySQL version 4.0.4
but mine is 3.23.54... can anyone enlighten me on how I can achieve the same
results with this older MySQL version? or can PHP 4.2.2 help me in any way?
Thank you so much!
Hwee
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php/mysql weblog - addnews function problems
Hey David,
Well basically I think it's a problem with the declaration of variables
and/or the way they are being processed by the form.
Here is the individual addNews function:
function addNews() {
global $db;
/* declare variables here? */
/* insert the new news entry */
$query = "INSERT INTO news" .
"VALUES('',$id,'{$_POST['postdate']}'," .
"'{$_POST['title']}',".
"'{$_POST['newstext']}')";
/* if statement to process the input? */
mysql_query($query);
echo "New News Entry Added!\n";
echo "Back to Blog";
/* adds a form where the administrator to add news */
echo "";
echo "";
echo "Make a new
entry: ";
echo "";
echo "Title: ";
echo "";
echo "News: ";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
}
and here is the error it produces so far (obviously I know that as it is it
wont work but my problem is that I don't know exactly what to do to it go
get it to work...:P)
Notice: Undefined variable: id in
c:\inetpub\wwwroot\webdev\phpblog\site\PHPblog.php on line 266
Notice: Undefined index: postdate in
c:\inetpub\wwwroot\webdev\phpblog\site\PHPblog.php on line 266
Notice: Undefined index: title in
c:\inetpub\wwwroot\webdev\phpblog\site\PHPblog.php on line 267
Notice: Undefined index: newstext in
c:\inetpub\wwwroot\webdev\phpblog\site\PHPblog.php on line 268
New News Entry Added!
Back to Blog
Slippy
"David Robley" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> In article <[EMAIL PROTECTED]>,
> [EMAIL PROTECTED] says...
> > hey,
> >
> > I've been working on a PHP/MYSQL weblog and I've got most of it working
> > apart from the adding new news function and a login section. I've set
the
> > form up but it is coming up with a few errors and the actual submitting
of
> > data doesn't work as of yet. The code is too long to include in this
post
> > and its hard to explain exactly where it is going wrong because I'm
fairly
> > new to PHP and MYSQL - this is my first stab at them both.
> >
> > Just wandering if anyone could take a couple of minutes to look at my
php
> > code and tell me what is wrong with it? (it's probably something
blindingly
> > obvious;))
> >
> > thanks very much
> >
> > slippy
> >
>
> Perhaps if you were to post the errors returned and the few lines of code
> leading up to the line where the error occurs...
>
>
> --
> Quod subigo farinam
>
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing on usenet?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Is it safe to do this?
That depends on what you're doing. Here's why I can't
provide feedback:
1) Your code isn't at all complete, the foreach
isn't even finished.
Not what matters here, I just snipped a bit of code in
here. I'm not even concerned about it because the
code works fine. I just need some work on a query.
2) Where does the $addresses variable come from and
what are you
trying todo with it?
Again it's a snipped code. $addresses collects
information from an emails. It searches for special
characters only, if no matches it does nothing, if a
match it runs a query. So looks for matches such as
@, +, _ for email addresses in the original email
sent, minus the headers.
3) What is the mysql table and its columns supposed
to represent?
What is the mysql table? I'm not sure what your
asking here. What's it for? For my site. Why do you
need me to explain that? I think my query explains
what's going on here. Another person responded to me
about this and new exactly what I was trying to do.
But for the record it's for a bouncing email script.
To handle bounced emails, again it's only a bit of
snipped code, the rest is fine.
4) What is this code supposed to accomplish?
Bounced emails, if an email is bounced it will do a
search and do something if and flag account if it's
bad. If it finds an email that is not in the DB it
also does nothing. (spam emails mainly go into that
account)
As for the query. I want to change a value of a field
when it reaches a certian value. So was wondering if
it was safe to change a value in one field once it
reaches a set value.
Okay with my program site emails that have a
email_setting of:
0 - All emails go to external inboxes and internal
inboxes
1 - All emails go to external inboxes and not internal
2 - All emails go to internal inboxes and not external
Each user can toggle what they want, ext. is real
email addresses... int. is the internal one with the
account which is not an email just an inbox.
So when it reaches a value of -5 means it's bounced 5
times therefore needs to be set to a lower value so
emails are not sent.
So I have:
field1
field2
field1 - is used for 0 , 1 , 2 value to send emails to
inboxes internal and external. Anything set in
negative figures no internal or external email will be
sent to that user.
field2 - is used to track the number of times the
email has bounced. So when it reaches 5 I need to
handle it and reset filed1 to a negative value.
So regarding my first example, it's no longer used by
me and won't go back to it. But is example 2 going to
work?
...
UPDATE users SET email_setting='-1' WHERE emailerror >
5
...
will that set email_setting to -1 if emailerror has a
value of 5 or more?
J
Jerry wrote:
> Hi,
>
> Is it safe to do this:
>
>
>
> foreach ($addresses as $email) {
> $body = str_replace($email, $comp_uri,
$body);
>mysql_query ("update users SET email_setting='-1'
> WHERE email='$email'") or die ("Can't set
> email_setting");
>mysql_query ("UPDATE users SET email_setting='-1'
WHERE emailerror > 5
") or die ("Unable ");
>
>
>
> Or would I be better to do this?:
>
>
>
> foreach ($addresses as $email) {
> $body = str_replace($email, $comp_uri,
$body);
>mysql_query ("update users SET emailerror='+1'
> WHERE email='$email'") or die ("Can't update
> email_setting");
>mysql_query ("UPDATE users SET email_setting='-1'
> WHERE emailerror > 5") or die ("Unable ");
>
>
>
> Suggestions welcome.
>
> Jerry
>
> Find local movie times and trailers on Yahoo!
Movies.
> http://au.movies.yahoo.com
--
--
paperCrane
Find local movie times and trailers on Yahoo! Movies.
http://au.movies.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
