Re: [PHP-DB] Query does not work
Once again you have provided the group with RANDOM pieces of code, completely out of context since you have already shown me that your query and db connection are being used in a function, hence your loss of $cxn. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query does not work
On 02/07/14 04:43, Ethan Rosenberg, PhD wrote: > while ($row31 = mysqli_fetch_row($result31)) { > printf ("%s %s %s %s %s\n", $row31[0], $row31[1], $row31[2], $row31[3]); Try print_r( $row31 ); > } // no output > > How can I loose a db connection in the middle of a program? This is not showing that you have, only that you can't see the actual data returned. I prefer firebird to mysql, and the normal process is to use the mysqli_fetch_assoc style working so that the keys of the array are the returned field names in which case the print_r output gives more information ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query does not work
Dear List - I think I've lost the database connection. I put in the following code: $sql31= "select Lname, Fname, Phone, Cust_Num from Customers order by Lname"; echo $sql31; //echos correctly $result31 = mysqli_query($cxn,$sql31); var_dump($result31); // result null while ($row31 = mysqli_fetch_row($result31)) { printf ("%s %s %s %s %s\n", $row31[0], $row31[1], $row31[2], $row31[3]); } // no output How can I loose a db connection in the middle of a program? TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query does not work
Dear List - Here is a query: select Lname, Fname, Payments, Charges, Balance from Customers, Charges where (Charges.Cust_Num = Customers.Cust_Num) and (Customers.Cust_Num =1022); Works beautifully from the terminal. In the php script: $sql31= "select Lname, Fname, Payments, Charges, Balance from Customers, Charges where (Charges.Cust_Num = Customers.Cust_Num) " . "and (Customers.Cust_Num =1022)"; if (!mysqli_query($cxn, $sql31)) printf("Errormessage: %s\n", mysqli_error($cxn)); I know something is wrong, since the output is "Errormessage:, however, the error is not stated. Dr. Google also has no ideas. He only says "if (!mysqli_query($cxn, $sql31)) printf("Errormessage: %s\n", mysqli_error($cxn));" I also know that the data base connection is OK, since $cxn = mysqli_connect($host,$user,$password); if (!$cxn) { die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error()); } if (mysqli_select_db($cxn, "Store") == 0) { printf("Errormessage: %s\n", mysqli_error($cxn)); die(); } and I ran a previous query which outputted the correct data. TIA Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query will not work - SOLVED
On Sat, Feb 2, 2013 at 8:08 PM, Ethan Rosenberg, PhD wrote: > I must be missing something fundamental!! Yes. > I was using the "worker@localhost", which did not have the Update privilege. > Added the privilege that, and everything worked. Please explain why you are not checking error returns from function calls? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query will not work - SOLVED
$sql13 = "UPDATE `Customers` SET `Lname` = 'Barnet', `City` = 'Lakewood', `State` = 'NJ' WHERE `Cust_Num` = 1089"; $result13 = mysqli_query($cxn, $sql13); if(mysqli_num_rows($result13)> 0) { $row_cnt = mysqli_num_rows($result13); echo "row count result13 is $row_cnt"; } else { echo "Ouch result13"; } mysqli_free_result($result13); $sql1 = "select * FROM `Customers` ORDER BY `Cust_Num`"; $result1 = mysqli_query($cxn, $sql1); if(mysqli_num_rows($result1)> 0) { $row_cnt = mysqli_num_rows($result1); echo "Row count result1 is $row_cnt"; } else { echo "Ouch result1"; } Try that.. Best, Karl On Feb 2, 2013, at 8:08 PM, Ethan Rosenberg, PhD wrote: I must be missing something fundamental!! Here is a code snippet: $sql13 = " UPDATE Customers SET Lname = 'Barnet', City = 'Lakewood', State = 'NJ' WHERE Cust_Num = 1089"; $result13 = mysqli_query($cxn, "UPDATE Customers SET Lname = 'Bleich', City = 'Lakewood', State = 'NJ' WHERE Cust_Num = 1089"); if($row_cnt = mysqli_num_rows($result13)!= 0) { $row_cnt = mysqli_num_rows($result13); echo "row count result13 is $row_cnt"; } else { echo "Ouch result13"; } mysqli_free_result($result13); $sql1 = "select Cust_Num, Fname, Lname, Street, City, State, Zip, Phone, Notes from Customers order by Cust_Num"; $result1 = mysqli_query($cxn, $sql1); if($row_cnt = mysqli_num_rows($result1)!= 0) { $row_cnt = mysqli_num_rows($result1); echo "Row count result1 is $row_cnt"; } else { echo "Ouch result1"; } Here are my results: Ouch result13 Row count result1 is 45 What am I doing wrong?? Ethan = I was using the "worker@localhost", which did not have the Update privilege. Added the privilege that, and everything worked. Sorry for bothering you with trivia. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query will not work - SOLVED
I must be missing something fundamental!! Here is a code snippet: $sql13 = " UPDATE Customers SET Lname = 'Barnet', City = 'Lakewood', State = 'NJ' WHERE Cust_Num = 1089"; $result13 = mysqli_query($cxn, "UPDATE Customers SET Lname = 'Bleich', City = 'Lakewood', State = 'NJ' WHERE Cust_Num = 1089"); if($row_cnt = mysqli_num_rows($result13)!= 0) { $row_cnt = mysqli_num_rows($result13); echo "row count result13 is $row_cnt"; } else { echo "Ouch result13"; } mysqli_free_result($result13); $sql1 = "select Cust_Num, Fname, Lname, Street, City, State, Zip, Phone, Notes from Customers order by Cust_Num"; $result1 = mysqli_query($cxn, $sql1); if($row_cnt = mysqli_num_rows($result1)!= 0) { $row_cnt = mysqli_num_rows($result1); echo "Row count result1 is $row_cnt"; } else { echo "Ouch result1"; } Here are my results: Ouch result13 Row count result1 is 45 What am I doing wrong?? Ethan = I was using the "worker@localhost", which did not have the Update privilege. Added the privilege that, and everything worked. Sorry for bothering you with trivia. Ethan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Question
That's a very good point, but since I use postgres that's one point that doesn't affect me and as such didn't cross my mind at the time. (Postgres uses schemas where mysql uses databases, a different database on postgres usually means a different database machine. And joining tables from different database machines isn't really plausible.) I read the question as 'schema names' in stead of 'database names'. On Mon, 2011-05-23 at 06:47 -0500, Bret Hughes wrote: > I like to set the dbname in a config file and use it only for making the > connection to the database. That way I can test/develop against > different databases on the same server. By locking yourself into > dbnames in the queries themselves I believe you loose a huge amount of > environmental flexibility. > > Using tablenames and or aliases is something I do a fair amount of but > only when joining or with complicated queries where clarity is an issue. > > On 05/23/2011 02:20 AM, maarten wrote: > > I would keep the db names and/or schema names in your queries. > > > > It clarifies what you are doing with the query making it easier for > > someone else to debug, improve, ... Certainly for those not familiar > > with your db structure. > > > > I am not aware of any drawbacks that can result from this. (Ok, maybe > > your program is a few bytes longer?) > > > > regards, > > Maarten > > > > On Sun, 2011-05-22 at 05:27 -0400, ad...@buskirkgraphics.com wrote: > >> I have been working on a class methods for some time now. > >> > >> > >> > >> I have reached a cross road when it comes to common practice of developing > >> query structure. > >> > >> > >> > >> Long ago I wrote queries where I just called the field I wanted on a > >> particular table unless I was joining them. > >> > >> > >> > >> Example: > >> > >> $query = " SELECT id FROM Table WHERE Clause"; > >> > >> > >> > >> Through time I developed a habit of queering as such. > >> > >> Example: > >> > >> $query = "SELECT tablename.id FROM db.table WHERE clause"; > >> > >> > >> > >> > >> > >> I have felt that, because my server contains multiple databases and I > >> needed > >> to jump between databases and tables without changing the connector this > >> always has been best practice for me. > >> > >> > >> > >> Someone recently told me, > >> > >> Rich, > >> > >> I do not agree with your design of the queries. > >> > >> There is no need to include the DB and table name in the query if you are > >> not joining tables. > >> > >> > >> > >> > >> > >> While I have a very hard time understanding this response as being valid. I > >> will propose the question. > >> > >> > >> > >> > >> > >> Is it bad practice to write queries with the database and table name in the > >> queries even if I am NOT joining tables? > >> > >> Is there an impact from PHP or MySQL that is caused by doing so? > >> > >> > >> > >> I know this more a MySQL question but as PHP developers we all deal with > >> queries on a day to day bases, > >> > >> and when developing more flexible class methods I build the queries in the > >> method. > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> Richard L. Buskirk > >> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Question
I would keep the db names and/or schema names in your queries. It clarifies what you are doing with the query making it easier for someone else to debug, improve, ... Certainly for those not familiar with your db structure. I am not aware of any drawbacks that can result from this. (Ok, maybe your program is a few bytes longer?) regards, Maarten On Sun, 2011-05-22 at 05:27 -0400, ad...@buskirkgraphics.com wrote: > I have been working on a class methods for some time now. > > > > I have reached a cross road when it comes to common practice of developing > query structure. > > > > Long ago I wrote queries where I just called the field I wanted on a > particular table unless I was joining them. > > > > Example: > > $query = " SELECT id FROM Table WHERE Clause"; > > > > Through time I developed a habit of queering as such. > > Example: > > $query = "SELECT tablename.id FROM db.table WHERE clause"; > > > > > > I have felt that, because my server contains multiple databases and I needed > to jump between databases and tables without changing the connector this > always has been best practice for me. > > > > Someone recently told me, > > Rich, > > I do not agree with your design of the queries. > > There is no need to include the DB and table name in the query if you are > not joining tables. > > > > > > While I have a very hard time understanding this response as being valid. I > will propose the question. > > > > > > Is it bad practice to write queries with the database and table name in the > queries even if I am NOT joining tables? > > Is there an impact from PHP or MySQL that is caused by doing so? > > > > I know this more a MySQL question but as PHP developers we all deal with > queries on a day to day bases, > > and when developing more flexible class methods I build the queries in the > method. > > > > > > > > > > > > Richard L. Buskirk > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Question
I have been working on a class methods for some time now. I have reached a cross road when it comes to common practice of developing query structure. Long ago I wrote queries where I just called the field I wanted on a particular table unless I was joining them. Example: $query = " SELECT id FROM Table WHERE Clause"; Through time I developed a habit of queering as such. Example: $query = "SELECT tablename.id FROM db.table WHERE clause"; I have felt that, because my server contains multiple databases and I needed to jump between databases and tables without changing the connector this always has been best practice for me. Someone recently told me, Rich, I do not agree with your design of the queries. There is no need to include the DB and table name in the query if you are not joining tables. While I have a very hard time understanding this response as being valid. I will propose the question. Is it bad practice to write queries with the database and table name in the queries even if I am NOT joining tables? Is there an impact from PHP or MySQL that is caused by doing so? I know this more a MySQL question but as PHP developers we all deal with queries on a day to day bases, and when developing more flexible class methods I build the queries in the method. Richard L. Buskirk
RE: [PHP-DB] Query syntax error?
Thanks, I got it working now, had to use the a/b/c thing a few times -Original Message- From: jose [mailto:jojap...@gmail.com] Sent: Thursday, January 13, 2011 9:52 AM Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Query syntax error? you have renamed places_data table to 'a' 2011/1/13 Harvey : > Hello, > > I have a query on a page that used to work fine, but is now generating an > error. > > I assume that the version of php or mysql was updated on the webhost server > or something like that? > > Here is the query: > > select count(places_data.place_id) as areacount, boroughs.borough_name as > boroname, area.area_name as areaname, area.area_id as areaid > from places_data a > inner join boroughs b on a.area_fid = b.area_id > inner join area c on c.borough_fid = b.borough_id > where places_data.on_off_fid = 2 > " . $cat_query . " > group by c.area_name > order by b.borough_name ASC, c.area_name ASC > > And here is the error message: > > Unknown column 'places_data.place_id' in 'field list' > > But place_id is a field in places_data table. > > Any ideas? > > Thanks! > > Harvey > > > -- > 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] Query syntax error?
you have renamed places_data table to 'a' 2011/1/13 Harvey : > Hello, > > I have a query on a page that used to work fine, but is now generating an > error. > > I assume that the version of php or mysql was updated on the webhost server > or something like that? > > Here is the query: > > select count(places_data.place_id) as areacount, boroughs.borough_name as > boroname, area.area_name as areaname, area.area_id as areaid > from places_data a > inner join boroughs b on a.area_fid = b.area_id > inner join area c on c.borough_fid = b.borough_id > where places_data.on_off_fid = 2 > " . $cat_query . " > group by c.area_name > order by b.borough_name ASC, c.area_name ASC > > And here is the error message: > > Unknown column 'places_data.place_id' in 'field list' > > But place_id is a field in places_data table. > > Any ideas? > > Thanks! > > Harvey > > > -- > 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] Query syntax error?
Hello, I have a query on a page that used to work fine, but is now generating an error. I assume that the version of php or mysql was updated on the webhost server or something like that? Here is the query: select count(places_data.place_id) as areacount, boroughs.borough_name as boroname, area.area_name as areaname, area.area_id as areaid from places_data a inner join boroughs b on a.area_fid = b.area_id inner join area c on c.borough_fid = b.borough_id where places_data.on_off_fid = 2 " . $cat_query . " group by c.area_name order by b.borough_name ASC, c.area_name ASC And here is the error message: Unknown column 'places_data.place_id' in 'field list' But place_id is a field in places_data table. Any ideas? Thanks! Harvey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query help
The most performant methods are to use well parameterized stored procedure or a prepared parameterized statement. : Ashay -Original Message- From: Niel Archer [mailto:n...@chance.now] Sent: Wednesday, November 17, 2010 6:30 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] query help > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a > DB. An eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 > columns > > I was thinking of having a single function which will perform the > insert on any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first > parameter the table name, and the second parameter is an array of > values which will be inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location so the > function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. You don't give any info about the database engine, but assuming you're using MySQL take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html Specifically you can use your idea to build an INSERT/VALUE version of the syntax INSERT INTO table (col1, col2, .colN.) VALUES (col1Value1, col2value1, colNvalue1), (col1Value2, col2value2, colNvalue2), ... -- Niel Archer niel.archer (at) blueyonder.co.uk -- 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] query help
Hey, You can also try PEAR module MDB2 to insert an array into a table. The function is: "executeMultiple". This works for other SQL queries too. Check this link please: http://pear.php.net/manual/en/package.database.mdb2.intro-execute.php Cheers, -Original Message- From: Artur Ejsmont [mailto:ejsmont.ar...@gmail.com] Sent: 17 noiembrie 2010 16:07 To: Vinay Kannan Cc: PHP DB; php mysql Subject: Re: [PHP-DB] query help well i guess you could do that. but it gets complicated after a while and there will be a lot of work and probably after a while you will get into some problems. You have to handle escaping, special types etc. Then what about performance? how to query the data ... using similar approach or is it just for inserts? there is a bit of work to make it really usable i guess. I am not saying its wrong though, I have seen this approach twice ... in general its possible. Maybe better choice would be to try to use some orm ? there are plenty of frameworks out there the only problem is the learning curve may be steep. What others think? art On 17 November 2010 13:51, Vinay Kannan wrote: > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a > DB. An eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 > columns > > I was thinking of having a single function which will perform the > insert on any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first > parameter the table name, and the second parameter is an array of > values which will be inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location so the > function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. > -- Visit me at: http://artur.ejsmont.org/blog/ -- 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] query help
> Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a DB. An > eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns > > I was thinking of having a single function which will perform the insert on > any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first parameter the > table name, and the second parameter is an array of values which will be > inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location > so the function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. You don't give any info about the database engine, but assuming you're using MySQL take a look at http://dev.mysql.com/doc/refman/5.0/en/insert.html Specifically you can use your idea to build an INSERT/VALUE version of the syntax INSERT INTO table (col1, col2, .colN.) VALUES (col1Value1, col2value1, colNvalue1), (col1Value2, col2value2, colNvalue2), ... -- Niel Archer niel.archer (at) blueyonder.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
From: "Vinay Kannan" To: "PHP DB" , "php mysql" Sent: Wednesday, November 17, 2010 2:51:35 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: [PHP-DB] query help Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan. Hi Vinay, You may want to try codeigniter. More info here. http://codeigniter.com/user_guide/database/active_record.html#insert with kind regards, Max. Max Kimambo Franz-Stenzer-Straße, 51 12679, Berlin. T: +493057706550 (new number) M: +4917649520175 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
On Wed, Nov 17, 2010 at 8:51 AM, Vinay Kannan wrote: > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a DB. An > eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns > > I was thinking of having a single function which will perform the insert on > any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first parameter the > table name, and the second parameter is an array of values which will be > inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location > so the function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. > codeigniter works this way. But they have a lot of extra functionality to keep the data safe for inserts. Check it out. -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query help
well i guess you could do that. but it gets complicated after a while and there will be a lot of work and probably after a while you will get into some problems. You have to handle escaping, special types etc. Then what about performance? how to query the data ... using similar approach or is it just for inserts? there is a bit of work to make it really usable i guess. I am not saying its wrong though, I have seen this approach twice ... in general its possible. Maybe better choice would be to try to use some orm ? there are plenty of frameworks out there the only problem is the learning curve may be steep. What others think? art On 17 November 2010 13:51, Vinay Kannan wrote: > Hello PHP Gurus, > > I need your help on an insert query. > > I wanted to know if there is way to insert an array of values into a DB. An > eg would explain this better : > > If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns > > I was thinking of having a single function which will perform the insert on > any insert which happens on the entire website. > > Eg : This function can be called with 2 parameters, the first parameter the > table name, and the second parameter is an array of values which will be > inserted into the table. > eg : Users has these columns [1]ID [2] Name [3]Location > so the function call would be something like * > insert_into_tbale(users,array[user_values])* > ** > Does this make sense ? Is this a good method to follow ? > > Thanks in advance ! > > Vinay Kannan. > -- Visit me at: http://artur.ejsmont.org/blog/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query help
Hello PHP Gurus, I need your help on an insert query. I wanted to know if there is way to insert an array of values into a DB. An eg would explain this better : If I have 2 tables in a DB, 1) users has 3 columns 2) hobbies = 5 columns I was thinking of having a single function which will perform the insert on any insert which happens on the entire website. Eg : This function can be called with 2 parameters, the first parameter the table name, and the second parameter is an array of values which will be inserted into the table. eg : Users has these columns [1]ID [2] Name [3]Location so the function call would be something like * insert_into_tbale(users,array[user_values])* ** Does this make sense ? Is this a good method to follow ? Thanks in advance ! Vinay Kannan.
Re: [PHP-DB] Query for duplicate records
On 18/10/10 06:55, Ron Piggott wrote: Is there a query you could help me write a SELECT query that would search table `ministry_profiles` for where column `organization` has the same organization more than once? I am trying to delete the duplicate organization records, but I am working with 1,000+ businesses and I can't go through each record looking for duplicates. select organization, count(*) from ministry_profiles group by organization having count(*) > 1; gives you which organization has more than 1 account (and how many duplicates you are dealing with). -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query for duplicate records
Is there a query you could help me write a SELECT query that would search table `ministry_profiles` for where column `organization` has the same organization more than once? I am trying to delete the duplicate organization records, but I am working with 1,000+ businesses and I can't go through each record looking for duplicates.
Re: [PHP-DB] Query stopping after 2 records?
Hawx wrote: On Mon, 27 Apr 2009 07:19:23 -0700, "Miller, Terion" wrote: I need help/advice figuring out why my query dies after 2 records. Here is the query: // Build your INSERT statement here $query = "INSERT into `warrants` (wid, name, age, warrant, bond, wnumber, crime) VALUES ("; $query .= " '$wid', '$name', '$age', '$warrant', '$bond', '$wnumber', '$crime' )"; $wid = mysql_insert_id(); // run query mysql_query($query) or die ("GRRR"); echo $query; It inserts two records and dies half way thru the 3rd? Thanks in advance for clues to fix this. T.Miller Make sure any apostrophes in the variable strings are escaped correctly. Check the addslashes() function. No - use the mysql_real_escape_string function. Addslashes is the wrong thing to use here. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query stopping after 2 records?
On Mon, 27 Apr 2009 07:19:23 -0700, "Miller, Terion" wrote: > I need help/advice figuring out why my query dies after 2 records. Here is > the query: > > // Build your INSERT statement here > > > $query = "INSERT into `warrants` (wid, name, age, warrant, bond, wnumber, > crime) VALUES ("; > $query .= " '$wid', '$name', '$age', '$warrant', > '$bond', '$wnumber', '$crime' )"; > $wid = mysql_insert_id(); > > // run query > > mysql_query($query) or die ("GRRR"); > > > echo $query; > > It inserts two records and dies half way thru the 3rd? > Thanks in advance for clues to fix this. > T.Miller Make sure any apostrophes in the variable strings are escaped correctly. Check the addslashes() function. -- Hawx -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query stopping after 2 records?
On Mon, Apr 27, 2009 at 10:19 AM, Miller, Terion < tmil...@springfi.gannett.com> wrote: > I need help/advice figuring out why my query dies after 2 records. Here is > the query: > > // Build your INSERT statement here > > > $query = "INSERT into `warrants` (wid, name, age, warrant, bond, wnumber, > crime) VALUES ("; >$query .= " '$wid', '$name', '$age', '$warrant', > '$bond', '$wnumber', '$crime' )"; >$wid = mysql_insert_id(); > > // run query > > mysql_query($query) or die ("GRRR"); > > > echo $query; > > It inserts two records and dies half way thru the 3rd? > Thanks in advance for clues to fix this. > T.Miller > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > echo out each sql statement and then change the or die to mysql_query($query) or die (mysql_error()); -- Bastien Cat, the other other white meat
[PHP-DB] Query stopping after 2 records?
I need help/advice figuring out why my query dies after 2 records. Here is the query: // Build your INSERT statement here $query = "INSERT into `warrants` (wid, name, age, warrant, bond, wnumber, crime) VALUES ("; $query .= " '$wid', '$name', '$age', '$warrant', '$bond', '$wnumber', '$crime' )"; $wid = mysql_insert_id(); // run query mysql_query($query) or die ("GRRR"); echo $query; It inserts two records and dies half way thru the 3rd? Thanks in advance for clues to fix this. T.Miller -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Fwd: [PHP-DB] query optimization
On Sun, Sep 28, 2008 at 6:28 PM, Chris <[EMAIL PROTECTED]> wrote: > Yves Sucaet wrote: > >> Hi Jack, >> >> I'm expecting less than 10 records in the resulting set. >> The BlockUnit table contains 337,253 records; the InteractionParts table >> contains 279,953 records. >> >> It takes currently 8.3 seconds to execute the query as I have it. >> >> Erh, this is embarassing but I'm going to need some help re-writing it >> with EXISTS... >> >> Thanks for the help so far, >> > > You haven't said which parts of the query is slow. > > Try: > > Just the subselect query. > If that's slow, try just the first part of the union. > If that's not slow, try the second part. > > At least you'll know where to concentrate. > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If possible, see if you can have the sort space on the db table made bigger. You could gain a lot of performance if you can adjust some the of settings in the mysql control file. sorry, hit reply instead of reply all (my bad) -- Bastien Cat, the other other white meat -- Bastien Cat, the other other white meat
Re: [PHP-DB] query optimization
Yves Sucaet wrote: Hi Jack, I'm expecting less than 10 records in the resulting set. The BlockUnit table contains 337,253 records; the InteractionParts table contains 279,953 records. It takes currently 8.3 seconds to execute the query as I have it. Erh, this is embarassing but I'm going to need some help re-writing it with EXISTS... Thanks for the help so far, You haven't said which parts of the query is slow. Try: Just the subselect query. If that's slow, try just the first part of the union. If that's not slow, try the second part. At least you'll know where to concentrate. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization - DB
MySQL < 5.0 can only use 1 index per table. MySQL >= 5.0 can use more than one via an index merge. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html On Sat, Sep 27, 2008 at 2:47 AM, Micah Gersten <[EMAIL PROTECTED]> wrote: > MySQL queries use 1 index per table, so to speed the query, we need to > know what indices you have for the 2 tables. > > Thank you, > Micah Gersten > onShore Networks > Internal Developer > http://www.onshore.com > > > > Yves Sucaet wrote: > > Oh, sorry I forgot to mention this. It's a MySQL database. > > > > - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> > > To: "YVES SUCAET" <[EMAIL PROTECTED]> > > Cc: > > Sent: Thursday, September 25, 2008 7:55 PM > > Subject: Re: [PHP-DB] query optimization > > > > > >> Other question is, what DB is this for? > >> > >> Thank you, > >> Micah Gersten > >> onShore Networks > >> Internal Developer > >> http://www.onshore.com > >> > >> > >> > >> YVES SUCAET wrote: > >>> How could I rewrite the following query so it runs faster: > >>> > >>> select distinct location from blockunit where blockid in ( > >>> select bu.blockid from blockunit bu inner join interactionparts ip on > >>> (bu.blockid = ip.part) > >>> where ip.blockid in > >>> > >>> > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > >>> > >>> > >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > >>> union > >>> select bu.blockid from blockunit bu > >>> where bu.blockid in > >>> > >>> > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > >>> > >>> > >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > >>> ) > >>> > >>> Thanks in advance, > >>> > >>> Yves > >>> > >>> > >>> > >>> > >> > > > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP-DB] query optimization - DB
Hi Micah, I'm learning here. Great! :-) How can I look this up? I'm pretty sure multiple fields are indexed. So should I specify explicitely which indices should be used? What fields do you think should be indexed? I do have control over the database and can create additional indices. Can you help out rewriting the query using EXISTS syntax? Thanks in advance, Yves - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> To: Sent: Friday, September 26, 2008 11:47 AM Subject: Re: [PHP-DB] query optimization - DB MySQL queries use 1 index per table, so to speed the query, we need to know what indices you have for the 2 tables. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Yves Sucaet wrote: Oh, sorry I forgot to mention this. It's a MySQL database. - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> To: "YVES SUCAET" <[EMAIL PROTECTED]> Cc: Sent: Thursday, September 25, 2008 7:55 PM Subject: Re: [PHP-DB] query optimization Other question is, what DB is this for? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: How could I rewrite the following query so it runs faster: select distinct location from blockunit where blockid in ( select bu.blockid from blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Thanks in advance, Yves -- 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] query optimization - DB
MySQL queries use 1 index per table, so to speed the query, we need to know what indices you have for the 2 tables. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Yves Sucaet wrote: > Oh, sorry I forgot to mention this. It's a MySQL database. > > - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> > To: "YVES SUCAET" <[EMAIL PROTECTED]> > Cc: > Sent: Thursday, September 25, 2008 7:55 PM > Subject: Re: [PHP-DB] query optimization > > >> Other question is, what DB is this for? >> >> Thank you, >> Micah Gersten >> onShore Networks >> Internal Developer >> http://www.onshore.com >> >> >> >> YVES SUCAET wrote: >>> How could I rewrite the following query so it runs faster: >>> >>> select distinct location from blockunit where blockid in ( >>> select bu.blockid from blockunit bu inner join interactionparts ip on >>> (bu.blockid = ip.part) >>> where ip.blockid in >>> >>> (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, >>> >>> >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) >>> union >>> select bu.blockid from blockunit bu >>> where bu.blockid in >>> >>> (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, >>> >>> >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) >>> ) >>> >>> Thanks in advance, >>> >>> Yves >>> >>> >>> >>> >> > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization - DB
Oh, sorry I forgot to mention this. It's a MySQL database. - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> To: "YVES SUCAET" <[EMAIL PROTECTED]> Cc: Sent: Thursday, September 25, 2008 7:55 PM Subject: Re: [PHP-DB] query optimization Other question is, what DB is this for? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: How could I rewrite the following query so it runs faster: select distinct location from blockunit where blockid in ( select bu.blockid from blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Thanks in advance, Yves -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
Hi Jack, I'm expecting less than 10 records in the resulting set. The BlockUnit table contains 337,253 records; the InteractionParts table contains 279,953 records. It takes currently 8.3 seconds to execute the query as I have it. Erh, this is embarassing but I'm going to need some help re-writing it with EXISTS... Thanks for the help so far, Yves - Original Message - From: Jack van Zanen To: Chris Cc: YVES SUCAET ; php-db@lists.php.net Sent: Thursday, September 25, 2008 7:49 PM Subject: Re: [PHP-DB] query optimization If you can answer the other questions that would help as well you can try rewriting using "exist" instead of "in" But without the basic information like number of records expected and explain plan it is very hard to come up with a better solution. Brgds Jack 2008/9/26 Chris <[EMAIL PROTECTED]> Jack van Zanen wrote: Hi If I am not mistaken, the second part of the union contains all rows that are in the first part of the union. just remove the first part. Kind of. The first part is a join, the second isn't. I was going to suggest rewriting the subquery into a single: where ip.blockid in (...) or bu.blockid in (...) however that'll probably be slower, but def. worth a try. -- Postgresql & php tutorials http://www.designmagick.com/ -- J.A. van Zanen
Re: [PHP-DB] query optimization
Other question is, what DB is this for? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: > How could I rewrite the following query so it runs faster: > > select distinct location from blockunit where blockid in ( > select bu.blockid from blockunit bu inner join interactionparts ip on > (bu.blockid = ip.part) > where ip.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > union > select bu.blockid from blockunit bu > where bu.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > ) > > Thanks in advance, > > Yves > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
If you can answer the other questions that would help as well you can try rewriting using "exist" instead of "in" But without the basic information like number of records expected and explain plan it is very hard to come up with a better solution. Brgds Jack 2008/9/26 Chris <[EMAIL PROTECTED]> > Jack van Zanen wrote: > >> Hi >> >> If I am not mistaken, >> the second part of the union contains all rows that are in the first part >> of >> the union. just remove the first part. >> > > Kind of. > > The first part is a join, the second isn't. > > I was going to suggest rewriting the subquery into a single: > > where > ip.blockid in (...) > or > bu.blockid in (...) > > however that'll probably be slower, but def. worth a try. > > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > -- J.A. van Zanen
Re: [PHP-DB] query optimization
Jack van Zanen wrote: Hi If I am not mistaken, the second part of the union contains all rows that are in the first part of the union. just remove the first part. Kind of. The first part is a join, the second isn't. I was going to suggest rewriting the subquery into a single: where ip.blockid in (...) or bu.blockid in (...) however that'll probably be slower, but def. worth a try. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
Hi If I am not mistaken, the second part of the union contains all rows that are in the first part of the union. just remove the first part. Also What is the table sizes of the tables? How many records are expected to come back from the union sub query? How many records are expected to come back from the main query What is the current execution plan? Jack 2008/9/26 YVES SUCAET <[EMAIL PROTECTED]> > How could I rewrite the following query so it runs faster: > > select distinct location from blockunit where blockid in ( > select bu.blockid from blockunit bu inner join interactionparts ip on > (bu.blockid = ip.part) > where ip.blockid in > > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > union > select bu.blockid from blockunit bu > where bu.blockid in > > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > ) > > Thanks in advance, > > Yves > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- J.A. van Zanen
Re: [PHP-DB] query optimization
Micah Gersten wrote: What indices do you have? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: How could I rewrite the following query so it runs faster: select distinct location from blockunit where blockid in ( select bu.blockid from blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Which parts are slow? Run the inner query by itself to see if that's slow. If it is, take the first part of the union and run that. Is that slow? Same for the second. Also since you're doing a DISTINCT in the outer query, you can change the subquery to do a UNION ALL. A UNION will remove duplicates from the result sets, a UNION ALL will not. Since you're doing a distinct on the whole thing anyway, remove the duplicate check from the subquery - it'll make it slightly faster. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
What indices do you have? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: > How could I rewrite the following query so it runs faster: > > select distinct location from blockunit where blockid in ( > select bu.blockid from blockunit bu inner join interactionparts ip on > (bu.blockid = ip.part) > where ip.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > union > select bu.blockid from blockunit bu > where bu.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > ) > > Thanks in advance, > > Yves > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query optimization
How could I rewrite the following query so it runs faster: select distinct location from blockunit where blockid in ( select bu.blockid from blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Thanks in advance, Yves -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query
You just drew my attention my Ron Piggott wrote: > > I have an interesting question. > > > > When I run the following query through my PHP script it produces 1 > > result. I know this because I echo the value of $num to the screen from > > the following syntax: $num=mysql_numrows($result); > > > > When I do the query in the "SQL" tab of phpMyAdmin there are 6 results. > > The 6 requests are correct. > > > > Any ideas why I have 2 different search results? > > Show us the php code.. maybe you're overwriting the $result variable in > your loop. > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query
Ron Piggott wrote: > I have an interesting question. > > When I run the following query through my PHP script it produces 1 > result. I know this because I echo the value of $num to the screen from > the following syntax: $num=mysql_numrows($result); > > When I do the query in the "SQL" tab of phpMyAdmin there are 6 results. > The 6 requests are correct. > > Any ideas why I have 2 different search results? Show us the php code.. maybe you're overwriting the $result variable in your loop. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query
I have an interesting question. When I run the following query through my PHP script it produces 1 result. I know this because I echo the value of $num to the screen from the following syntax: $num=mysql_numrows($result); When I do the query in the "SQL" tab of phpMyAdmin there are 6 results. The 6 requests are correct. Any ideas why I have 2 different search results? Ron SELECT * FROM ministry_directory INNER JOIN ministry_directory_listing_categories ON ministry_directory.entry = ministry_directory_listing_categories.ministry_directory_entry WHERE ministry_directory.listing_type = 2 AND ministry_directory_listing_categories.ministry_directory_category_reference IN ( 10 ) ORDER BY ministry_directory.name ASC -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Criteria
An easier way would be to use the built-in MySQL functions directly. That way, you avoid issues with date-format conversions between PHP and MySQL. Look at: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html You code then becomes: switch ($type) { case "today": $sql = "... where field = current_date()"; break; case "tomorrow": $sql = "... where field = (INTERVAL 1 DAY + current_date())"; break; case "week": $sql = "... where field = (INTERVAL 1 WEEK + current_date())"; break; default: die("Don't know what to do?"); HTH, Yves -- Original Message -- Received: Mon, 28 Apr 2008 01:32:31 PM CDT From: Nasreen Laghari <[EMAIL PROTECTED]> To: php-db@lists.php.net Subject: [PHP-DB] Query Criteria Hi All, I need help in below coding as it is not working. What I'm trying to do here, if $type contains "today" value then bring all record which has today's date, If $type contains "tomorrow" bring all tomorrow's record. Do you think below coding is correct? becuase when I run this query I get exception but if I place only one $query and outside of if.. else the same query runs without errors. Thank you for your help Regards $query; $date = date("d/m/y"); if($type=="today") { $query = "SELECT * FROM gig where gig_Date= $date"; } else if($type=="tomorrow") { $tomorrow = mktime(0, 0, 0, date("m") , date("d")+1, date("Y")); $query = "SELECT * FROM gig where gig_Date= $tomorrow"; } else if($type=="week") { $week = mktime(0, 0, 0, date("m") , date("d")+6, date("Y")); $query = "SELECT * FROM gig WHERE g.gig_Date <= ".$date." OR g.gig_Date >=".$week."; } $result = mysql_query($query)or die(mysql_error()); return $result; } Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Criteria
Hi All, I need help in below coding as it is not working. What I'm trying to do here, if $type contains "today" value then bring all record which has today's date, If $type contains "tomorrow" bring all tomorrow's record. Do you think below coding is correct? becuase when I run this query I get exception but if I place only one $query and outside of if.. else the same query runs without errors. Thank you for your help Regards $query; $date = date("d/m/y"); if($type=="today") { $query = "SELECT * FROM gig where gig_Date= $date"; } else if($type=="tomorrow") { $tomorrow = mktime(0, 0, 0, date("m") , date("d")+1, date("Y")); $query = "SELECT * FROM gig where gig_Date= $tomorrow"; } else if($type=="week") { $week = mktime(0, 0, 0, date("m") , date("d")+6, date("Y")); $query = "SELECT * FROM gig WHERE g.gig_Date <= ".$date." OR g.gig_Date >=".$week."; } $result = mysql_query($query)or die(mysql_error()); return $result; } Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [PHP-DB] Query Across DBMS Boundaries (Linked Database Servers?)
Dee Ayy wrote: Is there something which can perform a query across different database systems? It would extend the database.table.field notation to maybe dbms.database.table.field and allow queries across MySQL and MSSQL I don't think so. Both mysql and mssql would need to know how to parse that syntax and make it all work (besides other problems like database permissions, different connection strings and so on). Depending on how much data you are looking to move from one to the other, you could get mssql to export to a csv file (if it supports it - no idea) then get mysql to import it. Either use the csv storage engine (http://dev.mysql.com/doc/refman/4.1/en/csv-storage-engine.html) or load it into another table using 'load data infile' (http://dev.mysql.com/doc/refman/4.1/en/load-data.html). If you need to do it on the fly, my only other suggestion would be to set up two database connections - one for mssql and one for mysql and aggregate the data in php. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Across DBMS Boundaries (Linked Database Servers?)
Is there something which can perform a query across different database systems? It would extend the database.table.field notation to maybe dbms.database.table.field and allow queries across MySQL and MSSQL such as: //$sql = SELECT MySQLidentifier.aDatabase.aTable.aField, MSSQLidentifier.anotherDatabase.anotherTable.anotherField FROM MySQLidentifier.aDatabase.aTable, MSSQLidentifier.anotherDatabase.anotherTable WHERE MySQLidentifier.aDatabase.aTable.aKeyField = MSSQLidentifier.anotherDatabase.anotherTable.anotherKeyField //Or through JOINS, etc. I've seen the linked server option in MSSQL. Technically I can write to MSSQL, but company policy prohibits it. To steer clear of ambiguity, personally I've decided that I will also not create triggers (or any object) on MSSQL (which is a write in my mind). Basically I will only read information from MSSQL. I was thinking of PDO, which raises an interesting distinction of data-access abstraction layer versus database abstraction (which as I understand means no SQL rewrite or missing feature emulation -- SQL rewrite example: "TOP n" prefix versus "LIMIT n" suffix). The PDO developers may be close to creating such a feature. $MySQLidentifier = new PDO('mysql:host=localhost;dbname=test', $user, $pass); $MSSQLidentifier = new PDO('mssql:host=localhost;dbname=test', $user, $pass); $dbh = new PDO_LinkedServers($MySQLidentifier, $MSSQLidentifier); $dbh->exec("$sql"); Looking at MSSQL's OPENQUERY syntax, I don't even know if MSSQL provides this extended query notation. It just seems to allow a query on a remote (or distributed) server. Regards. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query table / results to an array
On Fri, Feb 29, 2008 at 12:22 AM, Ron Piggott <[EMAIL PROTECTED]> wrote: > $reference_number = mysql_result($search_result,$i,"reference_number"); > $description = mysql_result($search_result,$i,"description"); Just mind the line-wrapping in this message. ;-P -- Daniel P. Brown Senior Unix Geek -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query table / results to an array
Ron Piggott wrote: This line of code while ($row = mysql_fetch_assoc($result)) { Gave me this error message: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource I ran select reference, description from table in mySQL and it worked --- Any idea what caused the error? Oops.. $result = mysql_query("select id, description from table"); On Fri, 2008-02-29 at 16:39 +1100, Chris wrote: Ron Piggott wrote: I need help populating an array based on the output of a mysql query. $reference_number is a value assigned by auto_increment $description is what I want the value of the array to be --- a few words in length $reference_number = mysql_result($search_result,$i,"reference_number"); $description = mysql_result($search_result,$i,"description"); $references = array(); $result = "select id, description from table"; while ($row = mysql_fetch_assoc($result)) { $references[$row['id']] = htmlentities($row['description'], ENT_QUOTES, 'ISO-8859-1'); } print_r($references); -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query table / results to an array
Ron Piggott wrote: I need help populating an array based on the output of a mysql query. $reference_number is a value assigned by auto_increment $description is what I want the value of the array to be --- a few words in length $reference_number = mysql_result($search_result,$i,"reference_number"); $description = mysql_result($search_result,$i,"description"); $references = array(); $result = "select id, description from table"; while ($row = mysql_fetch_assoc($result)) { $references[$row['id']] = htmlentities($row['description'], ENT_QUOTES, 'ISO-8859-1'); } print_r($references); -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query table / results to an array
I need help populating an array based on the output of a mysql query. $reference_number is a value assigned by auto_increment $description is what I want the value of the array to be --- a few words in length $reference_number = mysql_result($search_result,$i,"reference_number"); $description = mysql_result($search_result,$i,"description"); Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query executing
The mysql_affected_rows() function will tell you how many rows were affected by the DELETE query you just fired. You can also test for TRUE or FALSE on the mysql_query() function if you just want to know if the query was successful or not. Rich -Original Message- From: ron.php [mailto:[EMAIL PROTECTED] Sent: Friday, October 05, 2007 9:52 AM To: php-db@lists.php.net Subject: [PHP-DB] Query executing How would I know if this mySQL query: DELETE FROM `table` WHERE `date_to_be_deleted` LIKE '$todays_date' actually deleted any rows from the table? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query executing
How would I know if this mySQL query: DELETE FROM `table` WHERE `date_to_be_deleted` LIKE '$todays_date' actually deleted any rows from the table? Ron
Re: [PHP-DB] Query syntax
SELECT * FROM table WHERE listing_type LIKE '%$listing_type%' AND (listing_approved = '1' OR listing_approved = '2') ORDER BY name ASC (notice the parentheses around the OR part of the clause) or you can do this... SELECT * FROM table WHERE listing_type LIKE '%$listing_type%' AND listing_approved IN ('1', '2') ORDER BY name ASC Good luck! -TG - Original Message - From: "ron.php" <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: Sat, 15 Sep 2007 15:04:59 -0400 Subject: [PHP-DB] Query syntax > $query="SELECT * FROM table WHERE listing_type LIKE '%$listing_type%' AND > listing_approved = '1' OR listing_approved = '2' ORDER BY name ASC"; > > My question is how can I search for records where listing_approved has a > value of either 1 or 2 (while in the same search I am searching for > listing_type --- ie other variables) > > Ron > > -- > 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] Query syntax
If I understood you correctly (and I'm not sure I did), this should do it $query="SELECT * FROM table WHERE listing_type LIKE '%$listing_type%' AND listing_approved IN( '1' ,'2') ORDER BY name ASC"; -- Niel Archer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query syntax
$query="SELECT * FROM table WHERE listing_type LIKE '%$listing_type%' AND listing_approved = '1' OR listing_approved = '2' ORDER BY name ASC"; My question is how can I search for records where listing_approved has a value of either 1 or 2 (while in the same search I am searching for listing_type --- ie other variables) Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Object in PHP?
Hi list, Is there any implementation of Query Object [1] written in PHP? I spent some time searching on the net but no clues're found. Thank you in advance. [1] http://www.martinfowler.com/eaaCatalog/queryObject.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query select value on MAX Date
At 08:47 25/02/2006, you wrote: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_=_NextPart_001_01C63997.1A6B1B7D" Date: Sat, 25 Feb 2006 08:07:36 +0900 Message-ID: <[EMAIL PROTECTED]> From: <[EMAIL PROTECTED]> To: <'php-db@lists.php.net'> Subject: FW: [PHP-DB] Query select value on MAX Date Dear All, Sorry, I wrote the wrong result. You've posted 3x with different results so it's too confusing to work out what you *want* to get. However I woudl write that query like so : SELECT MAX(Date) as NewestDate, Value FROM table GROUP BY Category HAVING Date=NewestDate HTH Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
FW: [PHP-DB] Query select value on MAX Date
Dear All, Sorry, I wrote the wrong result. The result I wanna get is : Id | Category | Date| Value 1 | A | 2005-02-21 | 2000 3 | B | 2006-01-20 | 4000 But I got : Id | Category | Date| Value 1 | A | 2005-02-21 | 3000 3 | B | 2006-01-20 | 5000 The MAX(Date) id correct, but the incorrect value... Thanks & Regards, Anita From: Adman, Nur anita Sent: Saturday, February 25, 2006 6:05 AM To: 'php-db@lists.php.net' Subject: RE: [PHP-DB] Query select value on MAX Date Dear All, I have table like this : Id | Category | Date| Value 1 | A | 2005-02-21 | 2000 2 | A | 2004-01-21 | 3000 3 | B | 2006-01-20 | 4000 4 | B | 2005-12-11 | 5000 I want to get the value for each Category where the date is MAX. So I make the query like this : "SELECT MAX(Date), Value FROM table GROUP BY Category" But I get is : Id | Category | Date| Value 1 | A | 2005-02-21 | 2000 3 | B | 2006-01-20 | 4000 Could you help me please? Thanks & Regards, Anita
RE: [PHP-DB] Query select value on MAX Date
Dear All, I have table like this : Id | Category | Date| Value 1 | A | 2005-02-21 | 2000 2 | A | 2004-01-21 | 3000 3 | B | 2006-01-20 | 4000 4 | B | 2005-12-11 | 5000 I want to get the value for each Category where the date is MAX. So I make the query like this : "SELECT MAX(Date), Value FROM table GROUP BY Category" But I get is : Id | Category | Date| Value 1 | A | 2005-02-21 | 2000 3 | B | 2006-01-20 | 4000 Could you help me please? Thanks & Regards, Anita
[PHP-DB] query on database determines image swap in php-coded page?
Hi, I'm a designer of simple webpages, and an enthusiastic user of PHP applications that run with MySQL -- but I don't write, or think in, PHP, so I can't quite figure how to do this: I run phpbb forum software (yes, I've asked at phpbb and at phpbbhacks, no luck) and I have installed for our members a chatroom kind of thing. In the header (an include on every page in the site), are .GIF images for navigation. I have installed a little hack (someone else's cleverness) that makes the private message button in the header swap to an animated gif when someone has a private message. And I'd like to do the same for the chatroom thing, which runs off the same database as the messaging system and the main forum. It would go kinda like: any page loads or refreshes, and header code checks the database for the number of chatters in the chatroom. Zero chatters = static button image, one or more chatters = animated gif button image. Can anyone help with this, or point me at a simple resource? I'd be happy to swap some work-time back, for the favour. Thanks for taking the time to read... -Elaine Email? My domain is elainemiller, with an extension of .com, and the thingy in front of the "at" sign would be "Elaine". -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query error
I've got a database table with a whole list of "windows" file paths. eg Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Binder.lnk Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Excel.lnk Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Office Setup.lnk Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Outlook.lnk Advent Tower PC\C\Program Files\Microsoft Office\Microsoft PowerPoint.lnk Advent Tower PC\C\Program Files\Microsoft Office\Microsoft Word.lnk Advent Tower PC\C\Program Files\Microsoft Office\MS Access Workgroup Administrator.lnk Advent Tower PC\C\Program Files\Microsoft Office\MSCREATE.DIR Advent Tower PC\C\Program Files\Microsoft Office\OF97SPEC.INI Advent Tower PC\C\Program Files\Microsoft Office\Office Advent Tower PC\C\Program Files\Microsoft Office\Office\1033 Now I want my DB query to search for directories .. here's a sample query select * from ff_files where full_path like 'Advent Tower PC\C\freeserve\help\images\%' doesnt work as the % is taken as a literal '%' I've aslso tried like 'Advent Tower PC\\C\\freeserve\\help\\images\%' I'm completely lost .. all I want is to return all rows that start with 'Advent Tower PC\C\freeserve\help\images\' tia Pete -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query that generates href links
Hi, Please send the code that you used with the hrefs so we can see where the error occurs. Tell us what line the error occured (and what line in your copied code that is). Jos -Original Message- From: John Burroughs [mailto:[EMAIL PROTECTED] Sent: 21 March 2005 03:29 To: php-db@lists.php.net Subject: [PHP-DB] Query that generates href links Hi everyone, I've created a database for my poetry that I've written. I have a table called Titles. It is made up of: title_id (a different number for every poem title. It's the primary key.) title (the title of the poem) title_date (the date I wrote the poem. stored in Date format.) I want to generate an html page that lists all of the poems that I've written in descending order by date. I want each title to be in an href link so my visitors can just click on a link, which will automatically generate a page to display that poem. So for example, each finished line would look like . . . Poem Title 1 - 1/25/05 Poem Title 2 - 1/20/05 Poem Title 3 - 1/15/05 This is my code fragment. This works for printing the Titles and dates in descending order, but that was as far as I could get. //Select the poetry database if ([EMAIL PROTECTED]("bdweb320883_poetry")) { echo("Unable to locate the poetry " . "database at this time."); exit(); } //Result the titles of all of the poems that I've written $result = @mysql_query("Select title, title_date from titles order by title_date DESC"); if(!$result) { echo("Error performing query: " . mysql_error() . ""); exit(); } //Display the title of each poem while ($row = mysql_fetch_array($result)) { $poetrytitle = $row['title']; $poetrydate = $row['title_date']; echo("" . $poetrytitle . " - " . $poetrydate . ""); } How can I do this. Everytime I tried adding to the above code, I kept getting error messages in php saying that there was an unexpected '>'character on line such and such, but I don't know how I could even check to see which line had the error? (I'm using myphpadmin to manage my MySQL database.) Now my connection to my MySQL server for my online hosting company has been done for 12 hours and I haven't been able to work on it anymore. Does anyone know an easy way to do this? Thanks, John Burroughs -- John Burroughs http://johnaburroughs.com --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0511-1, 03/17/2005 Tested on: 3/20/2005 9:28:43 PM avast! - copyright (c) 1988-2004 ALWIL Software. http://www.avast.com -- 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] Query that generates href links
John Burroughs wrote: Hi everyone, I've created a database for my poetry that I've written. I have a table called Titles. It is made up of: [snip] How can I do this. Everytime I tried adding to the above code, I kept getting error messages in php saying that there was an unexpected '>'character on line such and such, but I don't know how I could even check to see which line had the error? (I'm using myphpadmin to manage my MySQL database.) Now my connection to my MySQL server for my online hosting company has been done for 12 hours and I haven't been able to work on it anymore. Does anyone know an easy way to do this? Thanks, John Burroughs Hi John, The line number is the line number of your php code where the error occurs. Looking at the code here, there shouldn't be anything wrong with it, the error is probably somewhere else in your code. I'd suggest you'd look for unterminated strings, like echo("hi there). grt, Evert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query that generates href links
Hi everyone, I've created a database for my poetry that I've written. I have a table called Titles. It is made up of: title_id (a different number for every poem title. It's the primary key.) title (the title of the poem) title_date (the date I wrote the poem. stored in Date format.) I want to generate an html page that lists all of the poems that I've written in descending order by date. I want each title to be in an href link so my visitors can just click on a link, which will automatically generate a page to display that poem. So for example, each finished line would look like . . . Poem Title 1 - 1/25/05 Poem Title 2 - 1/20/05 Poem Title 3 - 1/15/05 This is my code fragment. This works for printing the Titles and dates in descending order, but that was as far as I could get. //Select the poetry database if ([EMAIL PROTECTED]("bdweb320883_poetry")) { echo("Unable to locate the poetry " . "database at this time."); exit(); } //Result the titles of all of the poems that I've written $result = @mysql_query("Select title, title_date from titles order by title_date DESC"); if(!$result) { echo("Error performing query: " . mysql_error() . ""); exit(); } //Display the title of each poem while ($row = mysql_fetch_array($result)) { $poetrytitle = $row['title']; $poetrydate = $row['title_date']; echo("" . $poetrytitle . " - " . $poetrydate . ""); } How can I do this. Everytime I tried adding to the above code, I kept getting error messages in php saying that there was an unexpected '>'character on line such and such, but I don't know how I could even check to see which line had the error? (I'm using myphpadmin to manage my MySQL database.) Now my connection to my MySQL server for my online hosting company has been done for 12 hours and I haven't been able to work on it anymore. Does anyone know an easy way to do this? Thanks, John Burroughs -- John Burroughs http://johnaburroughs.com --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0511-1, 03/17/2005 Tested on: 3/20/2005 9:28:43 PM avast! - copyright (c) 1988-2004 ALWIL Software. http://www.avast.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query was empty - Strange problem
I do not see where you are populating $Query --> therefore your query would be empty -- On Mar 16, 2005, at 10:30 PM, Vinayakam Murugan wrote: Hi I have an application in which i am connecting to three different databases. I have three connect and query procedure. I am facing problems with one of these databases. --- $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd); $Ret = mysql_select_db ($Dbname, $Conn); if (!$Ret) { $Error = "Error opening Database : $Dbname "; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = "" . mysql_errno() . "---" . mysql_error() . ""; echo $Error; --- I am getting an Error - Query was empty. I have tried echoin $Query before and after the mysql_query. It shows a proper query which if I copy and execute in phpmyadmin, i get the required results. It is not workign through PHP only. Any pointers would be very helpful. -- Warm Regards Vinayak -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * Stephen Johnson [EMAIL PROTECTED] http://www.thelonecoder.com --continuing the struggle against bad code-- * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query was empty - Strange problem
Here is the entire function function Executequery($Query) { global $Error, $Hostname,$Loginname,$Passwd,$Dbname,$Conn; $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd); $Ret = mysql_select_db ($Dbname, $Conn); if (!$Ret) { $Error = "Error opening Database : $Dbname "; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = "" . mysql_errno() . "---" . mysql_error() . ""; if (!$Error) { $numfields = mysql_num_fields($Ret); $numrows = mysql_num_rows($Ret); if($numrows == 0) { $Error = "Query returned zero records"; return FALSE; } } else { return FALSE; } } On Wed, 16 Mar 2005 22:36:13 -0800, Stephen Johnson <[EMAIL PROTECTED]> wrote: > I do not see where you are populating $Query --> therefore your query > would be empty -- > > > On Mar 16, 2005, at 10:30 PM, Vinayakam Murugan wrote: > > > Hi > > > > I have an application in which i am connecting to three different > > databases. I have three connect and query procedure. I am facing > > problems with one of these databases. > > > > --- > > > > $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd); > > > > $Ret = mysql_select_db ($Dbname, $Conn); > > > > if (!$Ret) > > { > > $Error = "Error opening Database : $Dbname "; > > return FALSE; > > } > > > > $Ret = mysql_query ($Query, $Conn) or $Error = "" . > > mysql_errno() . "---" . mysql_error() . ""; > > > > echo $Error; > > --- > > > > > > I am getting an Error - Query was empty. > > > > I have tried echoin $Query before and after the mysql_query. It shows > > a proper query which if I copy and execute in phpmyadmin, i get the > > required results. It is not workign through PHP only. > > > > > > Any pointers would be very helpful. > > > > -- > > Warm Regards > > > > Vinayak > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > * > Stephen Johnson > [EMAIL PROTECTED] > http://www.thelonecoder.com > > --continuing the struggle against bad code-- > * > > -- Warm Regards Vinayak -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query was empty - Strange problem
This is a common procedure. $Query is being passed to it. As I had said before, i had tried echoing $Query before and after the mysql_query statement and it displays a proper query. That's what driving me nuts! :-( On Wed, 16 Mar 2005 22:36:13 -0800, Stephen Johnson <[EMAIL PROTECTED]> wrote: > I do not see where you are populating $Query --> therefore your query > would be empty -- > > > On Mar 16, 2005, at 10:30 PM, Vinayakam Murugan wrote: > > > Hi > > > > I have an application in which i am connecting to three different > > databases. I have three connect and query procedure. I am facing > > problems with one of these databases. > > > > --- > > > > $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd); > > > > $Ret = mysql_select_db ($Dbname, $Conn); > > > > if (!$Ret) > > { > > $Error = "Error opening Database : $Dbname "; > > return FALSE; > > } > > > > $Ret = mysql_query ($Query, $Conn) or $Error = "" . > > mysql_errno() . "---" . mysql_error() . ""; > > > > echo $Error; > > --- > > > > > > I am getting an Error - Query was empty. > > > > I have tried echoin $Query before and after the mysql_query. It shows > > a proper query which if I copy and execute in phpmyadmin, i get the > > required results. It is not workign through PHP only. > > > > > > Any pointers would be very helpful. > > > > -- > > Warm Regards > > > > Vinayak > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > * > Stephen Johnson > [EMAIL PROTECTED] > http://www.thelonecoder.com > > --continuing the struggle against bad code-- > * > > -- Warm Regards Vinayak -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query was empty - Strange problem
Hi I have an application in which i am connecting to three different databases. I have three connect and query procedure. I am facing problems with one of these databases. --- $Conn = mysql_pconnect($Hostname,$Loginname,$Passwd); $Ret = mysql_select_db ($Dbname, $Conn); if (!$Ret) { $Error = "Error opening Database : $Dbname "; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = "" . mysql_errno() . "---" . mysql_error() . ""; echo $Error; --- I am getting an Error - Query was empty. I have tried echoin $Query before and after the mysql_query. It shows a proper query which if I copy and execute in phpmyadmin, i get the required results. It is not workign through PHP only. Any pointers would be very helpful. -- Warm Regards Vinayak -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query is not executing data is not entering in created database
amol patil wrote: hallo, see below code. query is not executing and data is not shown in table of database.after submit button hit. how to evaluate $submit true. have Tried these statements before if statement echo "--$submit--"; echo "--$_POST["submit"]--"; echo"Submit = $submit "; but still query is not executing. The reason people told you to put the above code is for troubleshooting. It's not going to fix your query. What do you actually see resulting from the above code before and after you hit your submit button? My crystal ball says you see this: Submit = Am I right? if($submit) { $dbh=mysql_connect ("localhost", "root") or die ('I cannot connect to the database because: ' . mysql_error()); mysql_select_db ("dollar1_allinfo"); mysql_query("INSERT INTO totalinfo (Username,Password) VALUES ('$loginusername','$loginpassword')")or die (mysql_error()); So if you see what I guessed above, then $submit is not set, so whatever code you have here not going to execute. Do you know how an IF conditional works? Do you know that if it evaluates to FALSE, then nothing within the conditional block is executed? Your query is probably fine, but the code is just never getting to it. I could tell you to use $_GET['submit'] or $_REQUEST['submit'], but would you know why or where to use it? -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query is not executing data is not entering in created database
hallo, see below code. query is not executing and data is not shown in table of database.after submit button hit. how to evaluate $submit true. have Tried these statements before if statement echo "--$submit--"; echo "--$_POST["submit"]--"; echo"Submit = $submit "; but still query is not executing. suggest changes in above code to execute query. thank you. - Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more.
RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain
"Gryffyn, Trevor" <[EMAIL PROTECTED]> wrote on 11/11/2004 01:39:37 PM: > This is a common join issue. If you don't specify ANYTHING to connect > between the two tables, it'll do one row from the first table, then ALL > the rows from the second. Row #2 from the first, then ALL the rows from > the second. > Just read the last thing you wrote.. Let me revise what I said then. > > You say you want everything where there's an entry in Dealers, but no > corresponding entry in blackgate_users eh? That's an outer join. Try > this (syntax happy with SQL Server, I don't use MySQL a lot so it might > be slightly different): > > SELECT dealers.account_no, >dealers.DealerName, >blackgate_users.User_Name, >blackgate_users.DealerName > FROM dealers left join blackgate_users on dealers.DealerName = > blackgate_users.DealerName > WHERE blackgate_users.DealerName is null Thanks for the help. That gets me much closer. I did a count in both tables and figured there should be 121 rows returned by the query. The above select statement gets me 141 rows returned. With a little sleuthing around in there I will probably figure out what the extra 10 rows are. Thanks you very much. Regards, Chip > What this says is take everything in Dealers, left join it against > blackgate_users (left join says to take everything from the left side.. > And match against the right side but leave NULL entries where there's no > match). Then we tell it that the field we want to compare is > DealnerName in both cases. The WHERE clause says only show us where > DealerName is null (meaning no corresponding record in blackgate_users). > > > I think that'll do it for ya. > > -TG > > > > -----Original Message- > > From: Chip Wiegand [mailto:[EMAIL PROTECTED] > > Sent: Thursday, November 11, 2004 4:28 PM > > To: PHP DB > > Subject: [PHP-DB] query of two tables returns too many rows, > > many more than the two tables contain > > > > > > I have two tables I want to get out the rows that are > > different between > > them. The results I am getting is almost 50,000 rows, but the > > two tables, > > combined, contain only about 600 rows total. Here is the > > select statement > > - > > > > SELECT dealers.account_no, dealers.DealerName, > > blackgate_users.User_Name, > > blackgate_users.DealerName > > FROM dealers, blackgate_users > > WHERE dealers.account_no NOT > > LIKE blackgate_users.User_Name > > > > in these tables the > > dealers.account_no is the same data as the blackgate_users.User_Name > > dealers.DealerName is the same data as the blackgate_users.DealerName > > I just want the rows that are in the dealers table but not in the > > blackgate_users table. > > > > Thanks for any help, > > Chip Wiegand > > Computer Services > > Simrad, Inc > > 425-778-8821 > > 425-771-7211 (FAX) > > > > -- > > 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] query of two tables returns too many rows, many more than the two tables contain
-Original Message- From: Chip Wiegand [mailto:[EMAIL PROTECTED] Sent: Thursday, November 11, 2004 3:28 PM To: PHP DB Subject: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain I have two tables I want to get out the rows that are different between them. <> in these tables the dealers.account_no is the same data as the blackgate_users.User_Name dealers.DealerName is the same data as the blackgate_users.DealerName I just want the rows that are in the dealers table but not in the blackgate_users table. === You want to do a join on the two tables, and make it conditional upon a known failing value (e.g. if when they DO match up, blackgate_users shouldn't be NULL - make that your condition). That will return just the rows in the first table that don't have a match. cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query of two tables returns too many rows, many more than the two tables contain
This is a common join issue. If you don't specify ANYTHING to connect between the two tables, it'll do one row from the first table, then ALL the rows from the second. Row #2 from the first, then ALL the rows from the second. If you had 600 rows in each table, you'd end up with 360,000 rows as a returned result. Chances are, you're just not being specific about what links the two tables. You might try something like this: SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name, blackgate_users.DealerName FROM dealers, blackgate_users WHERE dealers.account_no <> blackgate_users.User_Name AND dealers.DealerName = blackgate_users.DealerName Or something like that. Just read the last thing you wrote.. Let me revise what I said then. You say you want everything where there's an entry in Dealers, but no corresponding entry in blackgate_users eh? That's an outer join. Try this (syntax happy with SQL Server, I don't use MySQL a lot so it might be slightly different): SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name, blackgate_users.DealerName FROM dealers left join blackgate_users on dealers.DealerName = blackgate_users.DealerName WHERE blackgate_users.DealerName is null What this says is take everything in Dealers, left join it against blackgate_users (left join says to take everything from the left side.. And match against the right side but leave NULL entries where there's no match). Then we tell it that the field we want to compare is DealnerName in both cases. The WHERE clause says only show us where DealerName is null (meaning no corresponding record in blackgate_users). I think that'll do it for ya. -TG > -Original Message- > From: Chip Wiegand [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 11, 2004 4:28 PM > To: PHP DB > Subject: [PHP-DB] query of two tables returns too many rows, > many more than the two tables contain > > > I have two tables I want to get out the rows that are > different between > them. The results I am getting is almost 50,000 rows, but the > two tables, > combined, contain only about 600 rows total. Here is the > select statement > - > > SELECT dealers.account_no, dealers.DealerName, > blackgate_users.User_Name, > blackgate_users.DealerName > FROM dealers, blackgate_users > WHERE dealers.account_no NOT > LIKE blackgate_users.User_Name > > in these tables the > dealers.account_no is the same data as the blackgate_users.User_Name > dealers.DealerName is the same data as the blackgate_users.DealerName > I just want the rows that are in the dealers table but not in the > blackgate_users table. > > Thanks for any help, > Chip Wiegand > Computer Services > Simrad, Inc > 425-778-8821 > 425-771-7211 (FAX) > > -- > 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] query of two tables returns too many rows, many more than the two tables contain
I have two tables I want to get out the rows that are different between them. The results I am getting is almost 50,000 rows, but the two tables, combined, contain only about 600 rows total. Here is the select statement - SELECT dealers.account_no, dealers.DealerName, blackgate_users.User_Name, blackgate_users.DealerName FROM dealers, blackgate_users WHERE dealers.account_no NOT LIKE blackgate_users.User_Name in these tables the dealers.account_no is the same data as the blackgate_users.User_Name dealers.DealerName is the same data as the blackgate_users.DealerName I just want the rows that are in the dealers table but not in the blackgate_users table. Thanks for any help, Chip Wiegand Computer Services Simrad, Inc 425-778-8821 425-771-7211 (FAX) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query parser
We have a content management system and one of its features is what we call a "databse browser". What it allows you to do is take a table and display contents in the way it is useful for the relevant audience. One of the new features that was requested was editing the content which will in turn be moderated. All pretty straight forward when you are dealing with individual tables. Now it starts getting a little complex when we involve queries. This database browser can also accept queries as input and display the output. It pretty much takes the (pre-detremined) query sends it to mysql and dumps the result out. Now how do we let people edit these results. Now keep in mind any type of query that mysql supports can be used. One way would be to find out columns are obtained as a result of this query and store them in a temporary table and when they are updated change them in their respective tables. Seems too easy. Any other suggestions or ideas? --Turi -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query parser
We have a content management system and one of its features is what we call a "databse browser". What it allows you to do is take a table and display contents in the way it is useful for the relevant audience. One of the new features that was requested was editing the content which will in turn be moderated. All pretty straight forward when you are dealing with individual tables. Now it starts getting a little complex when we involve queries. This database browser can also accept queries as input and display the output. It pretty much takes the (pre-detremined) query sends it to mysql and dumps the result out. Now how do we let people edit these results. Now keep in mind any type of query that mysql supports can be used. One way would be to find out columns are obtained as a result of this query and store them in a temporary table and when they are updated change them in their respective tables. Seems too easy. Any other suggestions or ideas? --Turi -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query returns duplicate rows
On Wed, 21 Jul 2004 17:09:35 -0400, Brock Jimmy D Contr 74 MDSS/SGSI <[EMAIL PROTECTED]> wrote: > These two tables do not have a relation, so there is nothing to join. > > Basically, what I'm trying to is this: > Users need to score elements. The elements are assoicatied with standards. > A standard may have 1 to 29 elements associated with it. > > For users to score the elements they need to see the criteria for that particular > element. > > Thanks for the help though. > > You may want to try selecting all selected elements, then all selected scores, then loop through them individually. Doing a join without an ON clause is IMHO not a good choice. > > -Original Message- > From: Justin Patrin > To: Brock Jimmy D Contr 74 MDSS/SGSI > Cc: [EMAIL PROTECTED] > Sent: 7/21/2004 5:02 PM > Subject: Re: [PHP-DB] Query returns duplicate rows > > On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI > <[EMAIL PROTECTED]> wrote: > > My query is returning duplicates rows. > > > > table: elements > > elementId > > standardId > > elementtext > > category > > mcode > > linenum > > > > table: scores > > scoreId > > taskId > > scores > > > > Here's my query: > > SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId > > FROM elements, scores > > WHERE scores.taskId='12' > > AND elements.standardId='APR.05' > > > > This is returning duplicate rows, even though I'm using the DISTINCT > keyword. > > > > If I remove the field scoreId it is fine. > > > > Any suggestions? > > > > Should you perhaps be doing some kind of ON clause to that join? It's > joining every record in elements with every element in scores. You > need to tell it what fields to join on. > > Also, doing a distinct on that many fields at once could be very > expensive. Better tp refine your query or data model. > > -- > DB_DataObject_FormBuilder - The database at your fingertips > http://pear.php.net/package/DB_DataObject_FormBuilder > > paperCrane --Justin Patrin-- > -- DB_DataObject_FormBuilder - The database at your fingertips http://pear.php.net/package/DB_DataObject_FormBuilder paperCrane --Justin Patrin-- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query returns duplicate rows
These two tables do not have a relation, so there is nothing to join. Basically, what I'm trying to is this: Users need to score elements. The elements are assoicatied with standards. A standard may have 1 to 29 elements associated with it. For users to score the elements they need to see the criteria for that particular element. Thanks for the help though. -Original Message- From: Justin Patrin To: Brock Jimmy D Contr 74 MDSS/SGSI Cc: [EMAIL PROTECTED] Sent: 7/21/2004 5:02 PM Subject: Re: [PHP-DB] Query returns duplicate rows On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI <[EMAIL PROTECTED]> wrote: > My query is returning duplicates rows. > > table: elements > elementId > standardId > elementtext > category > mcode > linenum > > table: scores > scoreId > taskId > scores > > Here's my query: > SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId > FROM elements, scores > WHERE scores.taskId='12' > AND elements.standardId='APR.05' > > This is returning duplicate rows, even though I'm using the DISTINCT keyword. > > If I remove the field scoreId it is fine. > > Any suggestions? > Should you perhaps be doing some kind of ON clause to that join? It's joining every record in elements with every element in scores. You need to tell it what fields to join on. Also, doing a distinct on that many fields at once could be very expensive. Better tp refine your query or data model. -- DB_DataObject_FormBuilder - The database at your fingertips http://pear.php.net/package/DB_DataObject_FormBuilder paperCrane --Justin Patrin-- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query returns duplicate rows
On Wed, 21 Jul 2004 16:29:37 -0400, Brock Jimmy D Contr 74 MDSS/SGSI <[EMAIL PROTECTED]> wrote: > My query is returning duplicates rows. > > table: elements > elementId > standardId > elementtext > category > mcode > linenum > > table: scores > scoreId > taskId > scores > > Here's my query: > SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId > FROM elements, scores > WHERE scores.taskId='12' > AND elements.standardId='APR.05' > > This is returning duplicate rows, even though I'm using the DISTINCT keyword. > > If I remove the field scoreId it is fine. > > Any suggestions? > Should you perhaps be doing some kind of ON clause to that join? It's joining every record in elements with every element in scores. You need to tell it what fields to join on. Also, doing a distinct on that many fields at once could be very expensive. Better tp refine your query or data model. -- DB_DataObject_FormBuilder - The database at your fingertips http://pear.php.net/package/DB_DataObject_FormBuilder paperCrane --Justin Patrin-- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query returns duplicate rows
My query is returning duplicates rows. table: elements elementId standardId elementtext category mcode linenum table: scores scoreId taskId scores Here's my query: SELECT distinct elementtext,cateogy,mcode,linenum,scores,scoreId FROM elements, scores WHERE scores.taskId='12' AND elements.standardId='APR.05' This is returning duplicate rows, even though I'm using the DISTINCT keyword. If I remove the field scoreId it is fine. Any suggestions?
[PHP-DB] query problem..
Hi, I'm getting an unknown colum `num` in where clause error with this query: $options = mysql_query("select options.*, count(option_items.optionID) as `num` from options left join option_links using (optionID) left join option_items on options.optionID = option_items.optionID where option_links.productID = '$productID' and `num` > 0"); But I'm defining it with the agregate function in the second line.. Any idea why it's popping the error? -Micah -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query help
Hey Everyone, I can use some help here I have query where one can selects an "style" then a "area" and finally "rating" through a drop menu. When some selects a rating they select a range of ratings. For example: Style: Traditional Area: Yosemite Rating: From: 5.5 To: 5.10c This should pull up all the rock climbs that are in Yosemite, that are traditional style and are between the rating 5.5 to 5.10c. Here is my query: "SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC "; For some reason which I am not seeing, this query is not doing what it should be doing. Does anyone have any suggestions? Thanks, Craig -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query: Top 3 Results grouped by Category
Hi all, I am working on a search result query in which the user types a keyword and the top 3 results for each category are displayed with 24 or so total results on the page. The database is large (8 GB) and the table in which the fulltext search occurs has 3 million rows. Tables: product category category_link The category table contains many categories but I want to output only top level "general" categories. Each product has a link to its general category in the category_link table. The fulltext search runs fine if I don't attempt to order and group the output by categories. Currently I have 1 query that gets the top level categories (about 20 in all) then it loops through in PHP executing a fulltext search on EACH of those results. This probably isn't the best solution since it requires executing a fulltext search 20 different times and in some cases (especially general words or multiword searches) takes much too long. (anywhere from 2 - 45 seconds) Is there a way to combine this into 1 FAST query? I'm using MySQL 4.0.17 and PHP 4.3.4 so I don't think subqueries are an option... I suppose I could upgrade if it was totally necessary. The desired results for searching for "Caffeine" would look like this: -- Search results for "Caffeine" >Drinks - Caffeine Free Coke - Caffeine Free Pepsi - Jolt >Health Products - NoDoz - Generic Caffeine Tablets - Caffeine Free Sleepytime Tea >Books - Conquering Caffeine Dependence - Understanding Caffeine : A Biobehavioral Analysis - Over-the-Counter Drug Index 2004 >Music -DJ Micro - Caffeine: The Natural Stimulant -Magnet - Caffeine Superstar After each category I have a link to "See all Matches in Category XYZ" but that query runs fine since the dataset to search is reduced in size to just that particular category. The simplified pseudo-code of what I am currently using is this: GET TOP LEVEL CATEGORIES AND PLACE IN ARRAYS ( I left this code out) // the resulting arrays might look like this: $category_id = array("1","2","3"); $category_name = array("One","Two","Three"); for($i=0;$i $query = "SELECT P.product_id, P.product_name,...other stuff... FROM product AS P, category AS C, category_link AS CL INNER JOIN ... join necessary tables for images etc... WHERE MATCH (P.product_name) AGAINST ('" . $search_term . "') AND CL.product_id = P.product_id AND CL.category_id = '" . $category_id[$i] . "' LIMIT 0, 3 "; $result = mysql_query($query); print() // output the 3 results print("See all matches in " . $category_name[$i]); } Thanks for any help!!! - John
Re: [PHP-DB] Query for Most Recent Entry
Thompson, Jimi wrote: I'm trying to figure out how to write a MySQL query that will return the highest primary key a table. 1) Why? 2) SELECT MAX(id) FROM table 3) If you're trying to find the key of the last row inserted to an auto_increment column, use mysql_insert_id() or LAST_INSERT_ID() in PHP and MySQL, respectively. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals – www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query for Most Recent Entry
Hi, I'm trying to figure out how to write a MySQL query that will return the highest primary key a table. Thanks, Ms. Jimi Thompson, CISSP Manager, Web Operations Cox School of Business Southern Methodist University "What kind of peace do we seek? Not a 'Pax Americana' enforced on the world by American weapons of war. Not the peace of the grave or the security of a slave. I am talking about genuine peace, the kind of peace that makes life on earth worth living, the kind that enables men and nations to grow and to hope and to build a better life for their children-not merely peace for Americans, but peace for all men and women; not merely peace in our time, but peace for all time." - John F. Kennedy
[PHP-DB] Query Help!
Hi Everyone, Happy Easter to you all. I have this query, reasoning for posting on this Mailing List is under it. :) SELECT `username` , `score` FROM round" . $round_number . " WHERE 1 ORDER BY `score` DESC LIMIT 0, 30 Is there a way to make it output the $round_number -1 (minus one number)? I need this to be a temp. meassure as I only need the number previous to the current $round_number for a few days. Is there a way in the query I can subtrack the value by 1 and change it later when I want to show the current round? If it's too much hassles I can add $prev_round_number and change the values each week but thought there might be another way. J 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
Re: [PHP-DB] query repeating results???
Try left joining your states table also. MySQL may be joining the property_photos and the states table first, which would screw up your left join. Haven't had my morning coffee yet so I can't say for sure. Put EXPLAIN in front of your query to see what MySQL is doing. SELECT ... FROM properties LEFT JOIN states ON properties.state_id=states.id LEFT JOIN property_photos ON property_photos.property_id=properties.id WHERE states.code='fl' On Mar 26, 2004, at 1:45 AM, Katie Dewees wrote: I am running the following query: SELECT ...stuff... FROM properties LEFT JOIN property_photos ON property_photos.property_id=properties.id, states WHERE states.code='fl' AND properties.state_id=states.id LIMIT 0, 10 -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] query repeating results???
I am running the following query: SELECT ...stuff... FROM properties LEFT JOIN property_photos ON property_photos.property_id=properties.id, states WHERE states.code='fl' AND properties.state_id=states.id LIMIT 0, 10 (not sure if the JOIN is correct at all...I usually just join with the WHERE clauses, but I need to make sure that I get properties even when they do not have a photo - maybe this should be an LEFT OUTER JOIN?) "properties" is the main table (contains rental properties). "states" contains the names and codes of states. The "properties" table references the "states" table by an entry called "state_id" that, oddly enough, contains the id number from the "states" table. "property_photos" contains photos of the properties. There can be many "property_photo" entries for each property. "property_photos" references the "properties" table by the field "property_id". In this case, I only want one of the property photos returned...the one with property_photos.ordinal=1 (the one the customer ordered as first). I didn't fool with this right now, though, because there are NO photos in the database right now. This code is supposed to be searching for properties (think rental properties) that are in a particular state (florida in this case). The state is sent to the search program as a state code ("fl" in this case), not the id number of the state in the state table (I did this to make it look more readable to the user if seen in the query string - i.e. search.php?state=fl instead of state=10). There are only 7 properties entered into the database, but I am getting 10 results. It's giving me properties with id's 1-7, and then starting over again and giving me id's 1-3. All 7 should be listed as results since all 7 of them are in florida...but why is it repeating the result set and showing 10 results? When I take the LIMIT clause out, it gives me 315 results...the same repeating pattern again. It's late, and table joins confuse me...could someone please help? Please let me know if I should supply more information - I've tried to be thorough, but it's after midnight, and I need sleep. :-P Katie Dewees Web Developer E-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query to Array to Echo Problem--Thank you
Yes! That worked! (It was returning the same field values not column names, my mistake). Now it returns what it should return. Onwards to the next snafu! Thanks! "Ryan Jameson (USA)" <[EMAIL PROTECTED]> wrote: One thing may be that you need to do a fetch to increment the result row odbc_fetch_row($result);. Though that doesn't explain your column names being in the array unless that's what's in the table. <>< Ryan -Original Message- From: Karen Resplendo [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 5:18 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Query to Array to Echo Problem Here is the webpage that demonstrates my code problem: http://170.104.158.16/chemlatestPAGETEST.php3?pwsno=00100 I'm so close I can smell it. Hoping someone can troubleshoot my code. Here is the piece that doesn't work. It returns the 2 column names, over and over. For some reason I'm not loading the values from the query. (Using only 2 columns for now and hardcoding number of rows): function QueryIntoArray($query){ settype($retval,"array"); /* connect to sdwis server*/ $connectionSDWIS = odbc_connect("","",""); if(!$connectionSDWIS) die("Couldn't connect"); /* select sdwis database */ odbc_do($connectionSDWIS, "use "); $result= odbc_do($connectionSDWIS,$query); if(!$result){ print "Query Failed"; } for($i=0;$i<100;$i++){ $fields=odbc_num_fields($result); for($j=1;$j <$fields;$j++){ $retval[$i][odbc_field_name($result,$j)] = odbc_result($result,$j); }//end inner loop }//end outer loop return $retval; }//end function $query = "SELECT DISTINCT PWS, PWSName, CONVERT(varchar(12), DateCollected, 101), AnalyteName, SourceID, str(Results,7,4), str(MCL, 7,4), UOM, DateCollected FROM ChemMon WHERE PWSID='00100' AND AnalyteCode NOT IN('3013','3014','3100') ORDER BY DateCollected DESC, AnalyteName "; settype($myresult,"array"); $myresult = QueryIntoArray($query); for($i=0;$i print $myresult[$i]["AnalyteName"]; print $myresult[$i]["UOM"]; } - Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php - Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.
RE: [PHP-DB] Query to Array to Echo Problem
One thing may be that you need to do a fetch to increment the result row odbc_fetch_row($result);. Though that doesn't explain your column names being in the array unless that's what's in the table. <>< Ryan -Original Message- From: Karen Resplendo [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 5:18 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Query to Array to Echo Problem Here is the webpage that demonstrates my code problem: http://170.104.158.16/chemlatestPAGETEST.php3?pwsno=00100 I'm so close I can smell it. Hoping someone can troubleshoot my code. Here is the piece that doesn't work. It returns the 2 column names, over and over. For some reason I'm not loading the values from the query. (Using only 2 columns for now and hardcoding number of rows): function QueryIntoArray($query){ settype($retval,"array"); /* connect to sdwis server*/ $connectionSDWIS = odbc_connect("","",""); if(!$connectionSDWIS) die("Couldn't connect"); /* select sdwis database */ odbc_do($connectionSDWIS, "use "); $result= odbc_do($connectionSDWIS,$query); if(!$result){ print "Query Failed"; } for($i=0;$i<100;$i++){ $fields=odbc_num_fields($result); for($j=1;$j <$fields;$j++){ $retval[$i][odbc_field_name($result,$j)] = odbc_result($result,$j); }//end inner loop }//end outer loop return $retval; }//end function $query = "SELECT DISTINCT PWS, PWSName, CONVERT(varchar(12), DateCollected, 101), AnalyteName, SourceID, str(Results,7,4), str(MCL, 7,4), UOM, DateCollected FROM ChemMon WHERE PWSID='00100' AND AnalyteCode NOT IN('3013','3014','3100') ORDER BY DateCollected DESC, AnalyteName "; settype($myresult,"array"); $myresult = QueryIntoArray($query); for($i=0;$ihttp://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query to Array to Echo Problem
Here is the webpage that demonstrates my code problem: http://170.104.158.16/chemlatestPAGETEST.php3?pwsno=00100 I'm so close I can smell it. Hoping someone can troubleshoot my code. Here is the piece that doesn't work. It returns the 2 column names, over and over. For some reason I'm not loading the values from the query. (Using only 2 columns for now and hardcoding number of rows): function QueryIntoArray($query){ settype($retval,"array"); /* connect to sdwis server*/ $connectionSDWIS = odbc_connect("","",""); if(!$connectionSDWIS) die("Couldn't connect"); /* select sdwis database */ odbc_do($connectionSDWIS, "use "); $result= odbc_do($connectionSDWIS,$query); if(!$result){ print "Query Failed"; } for($i=0;$i<100;$i++){ $fields=odbc_num_fields($result); for($j=1;$j <$fields;$j++){ $retval[$i][odbc_field_name($result,$j)] = odbc_result($result,$j); }//end inner loop }//end outer loop return $retval; }//end function $query = "SELECT DISTINCT PWS, PWSName, CONVERT(varchar(12), DateCollected, 101), AnalyteName, SourceID, str(Results,7,4), str(MCL, 7,4), UOM, DateCollected FROM ChemMon WHERE PWSID='00100' AND AnalyteCode NOT IN('3013','3014','3100') ORDER BY DateCollected DESC, AnalyteName "; settype($myresult,"array"); $myresult = QueryIntoArray($query); for($i=0;$i
[PHP-DB] Re: php-db Query
Peppe Don't search by month at all - use a start and stop date then order by date. Now you have one result set with all the dates in it and you can use your PHP code to figure out where to close one table and open a net one: $result = mysql_query($sql); echo ""; $thisMonth = $result[3]; // I'm assuming this is a month year combo // you'll have to do more work here. while (!$result ->EOF) { if ($thisMonth != $result[3]) { $thisMonth = $result[3]; echo ''; } echo '' . $result[0] . ' ' . $result[1] . ''; $result->MoveNext(); // Moves to the next row } // end while echo ""; > > $rowArray = mysql_fetch_array($result); > $datum= $rowArray["datum"]; > $title =$rowArray["title"]; > $datum= date("d-m-Y", strtotime($datum)); > $datum is 20-02-2004 On 2/24/04 8:20 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > From: "peppe" <[EMAIL PROTECTED]> > Reply-To: "peppe" <[EMAIL PROTECTED]> > Date: Tue, 24 Feb 2004 17:20:19 +0100 > To: [EMAIL PROTECTED] > Subject: Query > > Hi I have this query > $sql = "SELECT * FROM events WHERE ( YEAR(datum) = '" . $year . "') ORDER BY > datum ASC"; > $result = mysql_query($sql); > $numRows = mysql_num_rows($result); > for ($row = 1; $row <= $numRows; $row++) { > > $rowArray = mysql_fetch_array($result); > $datum= $rowArray["datum"]; > $title =$rowArray["title"]; > $datum= date("d-m-Y", strtotime($datum)); > $datum is 20-02-2004 > ?> > I want to show here the events by month for example table with events of > 02(February) then if there are events from 03(March) another table and so > one > How can I make this work do I need to make another query > Any idea > -- Frank Flynn Poet, Artist & Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query
Hi I have this query $sql = "SELECT * FROM events WHERE ( YEAR(datum) = '" . $year . "') ORDER BY datum ASC"; $result = mysql_query($sql); $numRows = mysql_num_rows($result); for ($row = 1; $row <= $numRows; $row++) { $rowArray = mysql_fetch_array($result); $datum= $rowArray["datum"]; $title =$rowArray["title"]; $datum= date("d-m-Y", strtotime($datum)); $datum is 20-02-2004 ?> I want to show here the events by month for example table with events of 02(February) then if there are events from 03(March) another table and so one How can I make this work do I need to make another query Any idea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query and math
Hi, q1) i got a database with 2 diffrent tables, i want to take out all the information that arent duplicated from on of the tables, like this: $sql = "SELECT t1.rubrik, t1.info, t2.priser FROM table1 AS t1, table2 AS t2 WHERE t1.arkiv = '0' ORDER BY t1.rubrik"; the problem is that i got alot of duplicates (its for another purpose), so i need to filter out the duplicates in the rubrik field to get just one match each, like: id - rubrik - info 0 - - some info 1 - - some info 2 - - some info 3 - - some info 4 - - some info would print out 0 - - some info 1 - - some info 2 - - some info 3 - - some info q2) i got like 100 matches from a database into a $num variable, then i want to devide that by 3 aslong as it can be done, eg: while ($num != 0) { ïf ($num / 3 = true){ some code to display some fields from a database $num = $num - 3; } else { some other code to display $num = 0; } } basicly i want the if statement to check if its more then 3 posts left to print out, if not its gonna print out the remaining last 1 - 2 post in a diffrent way (for the looks) regards patrick -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Sum problem
From: "Larry Sandwick" <[EMAIL PROTECTED]> > I appreciate the quick response, but I should have been more clear. > > I understand the query below, but I would only have 2 total and it is > not group by the companies. > > The query below gives me the main information without totals. > > How do I add the 2 totals to the query below? > > SELECT DISTINCT(Company), account, City, State FROM Table WHERE number = > 100 SELECT Company, Account, City, State, SUM(IF(Status='Held',Cost,0)) AS Held, SUM(IF(Status='Open',Cost,0)) AS Cost FROM Table WHERE Number = 100 ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Problem
I need to sum the field *COST* in this query where data in *STATUS* is equal to "HELD" and "OPEN", so I will have 2 totals passed below and do not know where to begin . All information is in 1 table. Original query select distinct(Company_name), account, City, State from table where number = 100 I need to be able to pull the $$ amount totals by company name for status equal to HELD and OPEN . Result Company A OPEN HELD 1233100.0050.00 Any suggestion would be appreciated. Larry Sandwick Larry Sandwick Sarreid, Ltd. Network/System Administrator phone: (252) 291-1414 x223 fax : (252) 237-1592
Re: [PHP-DB] Query Sum problem
From: "Larry Sandwick" <[EMAIL PROTECTED]> > I need to sum the field *COST* in this query where data in *STATUS* is > equal to "HELD" and "OPEN", so I will have 2 totals passed below and do > not know where to begin . SELECT status, SUM(cost) FROM Table WHERE status IN ('HELD','OPEN') GROUP BY status ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Query Sum problem
I need to sum the field *COST* in this query where data in *STATUS* is equal to "HELD" and "OPEN", so I will have 2 totals passed below and do not know where to begin . All information is in 1 table. Original query select distinct(Company_name), account, City, State from table where number = 100 Failed miserably select distinct(Company_name) , account, City, State, sum(cost) from table where number= 100 and status="HELD" I need to be able to pull the $$ amount totals by company name for status equal to HELD and OPEN . Result Company A OPEN HELD 1233100.0050.00 Any suggestion would be appreciated. Larry Sandwick Sarreid, Ltd. Network/System Administrator phone: (252) 291-1414 x223 fax : (252) 237-1592
Re: [PHP-DB] Query Case In-sensitive
Yes you are right, I have tried the queries on Oracle and MySQL, it works right on MySQL (case insensitive) but not on Oracle. Thank you for the correction. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] ; [EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 2:36 PM Subject: Re: [PHP-DB] Query Case In-sensitive As an Oracle DBA (9+ years, versions 7.x - 9.x) I just wanted to make a correction. The LIKE command uses wild card characters ( _ and % ) for substitutions. Oracle IS a case-sensitive RDBMS and the LIKE command will NOT do case-insensitive queries. Roy A. Jones -- US Pharma Database Administration GlaxoSmithKline Inc. US Pharma IT, Shared Services -- External: (919) 483-0266 Internal: 703-0266 Fax: (919) 315-6842 Office: RC2 - 2005 Email: [EMAIL PROTECTED] -- http://usphdba.gsk.com/ - USPHARMA Database Site "mustafa ocak" <[EMAIL PROTECTED]> 19-Nov-2003 07:11 To [EMAIL PROTECTED] cc Subject Re: [PHP-DB] Query Case In-sensitive Use "LIKE" clause instead of "=" SELECT item_number FROM item WHERE item_code LIKE 'M1234' ; This will perform case-insensitive matching on all databases I used before (Including Oracle, MySQL, SQL-Server) - Original Message - From: "ramki" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 18, 2003 5:46 AM Subject: Re: [PHP-DB] Query Case In-sensitive > Generally in DBs, data is case sensitive. > > Simply you can do an uppercase or lowercase conversion in both sides. > > Ex : Select item number from item where upper(item_code) = upper("m1234"); > > -ramki > - Original Message - > From: "Larry Sandwick" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, November 18, 2003 1:23 AM > Subject: [PHP-DB] Query Case In-sensitive > > > > Is there a way to run a query so that it ignores the case, and the query > > is not case sensitive? > > > > The data being stored is "m1234" > > > > Query > > > > Select item number from item where item_code = "M1234" ; > > > > The query above should return the data above. > > > > TIA > > > > > > > > Larry Sandwick > > > > Sarreid, Ltd. > > > > Network/System Administrator > > > > phone: (252) 291-1414 x223 > > > > fax : (252) 237-1592 > > > > > > > > > > -- > 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