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
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 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_cntbr /; } else { echo Ouch result13br /; } 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_cntbr /; } else { echo Ouch result1br /; } 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_cntbr /; } else { echo Ouch result13br /; } 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_cntbr /; } else { echo Ouch result1br /; } 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
Re: [PHP-DB] Query will not work - SOLVED
On Sat, Feb 2, 2013 at 8:08 PM, Ethan Rosenberg, PhD erosenb...@hygeiabiomedical.com 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 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
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 syntax error?
you have renamed places_data table to 'a' 2011/1/13 Harvey har...@harveyk.com: 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
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 har...@harveyk.com: 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 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 viny...@gmail.com 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
Re: [PHP-DB] query help
On Wed, Nov 17, 2010 at 8:51 AM, Vinay Kannan viny...@gmail.com 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
From: Vinay Kannan viny...@gmail.com To: PHP DB php-db@lists.php.net, php mysql php_mysql_usergr...@yahoogroups.com 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
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
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 viny...@gmail.com 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
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 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
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
Re: [PHP-DB] Query stopping after 2 records?
On Mon, 27 Apr 2009 07:19:23 -0700, 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 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?
Hawx wrote: On Mon, 27 Apr 2009 07:19:23 -0700, 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 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 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
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 - 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: php-db@lists.php.net 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
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: php-db@lists.php.net 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: php-db@lists.php.net 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: php-db@lists.php.net 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 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: php-db@lists.php.net 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
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
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
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
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
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
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
You just drew my attention my include that brings in the site menu and was changing the conflict. Thanks :) Ron On Thu, 2008-05-01 at 13:19 +1000, Chris wrote: 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 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
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); ? $sql = SELECT reference_number FROM table WHERE this='whatever' OR that NOT LIKE '%whatever%' LIMIT 0,1; $result = mysql_query($sql); $row = mysql_fetch_array($result); ? Just mind the line-wrapping in this message. ;-P -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- 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
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 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
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 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
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 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
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 . . . pa href=poem.php?titleid=1Poem Title 1/a - 1/25/05/p pa href=poem.php?titleid=2Poem Title 2/a - 1/20/05/p pa href=poem.php?titleid=3Poem Title 3/a - 1/15/05/p 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(pUnable to locate the poetry . database at this time./p); 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(pError performing query: . mysql_error() . /p); exit(); } //Display the title of each poem while ($row = mysql_fetch_array($result)) { $poetrytitle = $row['title']; $poetrydate = $row['title_date']; echo(p . $poetrytitle . - . $poetrydate . /p); } 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 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 BR; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = BRBR . mysql_errno() . --- . mysql_error() . BRBR; 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
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 BR; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = BRBR . mysql_errno() . --- . mysql_error() . BRBR; 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 BR; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = BRBR . mysql_errno() . --- . mysql_error() . BRBR; 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
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 BR; return FALSE; } $Ret = mysql_query ($Query, $Conn) or $Error = BRBR . mysql_errno() . --- . mysql_error() . BRBR; 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 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]--; echoSubmit = $submit ; but still query is not executing. ?php 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()); } ? 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 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]--; echoSubmit = $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? ?php 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 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
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. snip 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
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. snip 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 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
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 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 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
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
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;$i100;$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.
[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 TABLE; $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 '/TABLETABLE'; } echo 'TR' . $result[0] . '/TDTD ' . $result[1] . '/TD/TR'; $result-MoveNext(); // Moves to the next row } // end while echo /TABLE; $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
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;$i100;$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;$icount($myresult);$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
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
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
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
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
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
Re: [PHP-DB] Query Case In-sensitive
you can convert both of the sides to one case, similar to this: select item_number from item where upper (item_code) = upper (M1234); this way you ensure that both the value in the database and the selection variable are in one and the same case: capital. this should work in just about every single database, there's always a function to convert a string to upper case, the syntax might be different to the above example (which is mysql). just consult your dbms documentation. ciao, andrei . Larry Sandwick [EMAIL PROTECTED] 2003/11/17 09:53 PM Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc 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
Re: [PHP-DB] Query Case In-sensitive
In Oracle you have a couple of choices. (1) If you can ensure that the data stored is in one case you can apply a function to the keyed in data SELECT item_number FROM item WHERE item_code = lower('M1234'); (2) If you can not ensure data case then you can apply the function to both side SELECT item_number FROM item WHERE lower(item_code) = lower('M1234'); You can use triggers on your tables to do case conversions then ensure case in the table. In later versions of Oracle you can apply function-based indexes to your table to speed up these 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 Larry Sandwick [EMAIL PROTECTED] 17-Nov-2003 14:53 Please respond to [EMAIL PROTECTED] To [EMAIL PROTECTED] cc 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
Re: [PHP-DB] Query Case In-sensitive
From: Larry Sandwick [EMAIL PROTECTED] Is there a way to run a query so that it ignores the case, and the query is not case sensitive? What's this have to do with PHP and what database are you using? ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Case In-sensitive
Hey! At least in Postgresql you can do something like: SELECT * FROM table where item_code ILIKE 'blabla'; But I guess it's standard, so you can use it in any DB. Larry Sandwick wrote: 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
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
Re: [PHP-DB] Query or code?
From: Peter Beckman [EMAIL PROTECTED] I have this data: Table Log: appid userid points datetype Table Score: appid userid score I want to verify that the last entry in table log of type x is equal to the sum of the scores in table score for the same appid and userid. Can I do this in SQL easily? My problem is selecting the correct (most recent) row in log in which to match the score. Basically I want a report of AppID, TeamMemberID, log.points, score.score that shows where points != score; Why do you have a score table at all? That's just repeating data when you can always do a SUM query on the log table to get the score... Athough... SQL example: mysql select * from log; ++--+ | id | log | ++--+ | 1 |1 | | 1 |2 | | 1 |3 | | 1 |4 | | 2 |3 | | 2 |5 | ++--+ 6 rows in set (0.00 sec) mysql select * from score; ++---+ | id | score | ++---+ | 1 |10 | | 2 | 1 | ++---+ 2 rows in set (0.00 sec) mysql select s.id, s.score, sum(l.log) as log_sum from score s, log l where l.id = s.id group by l.id; ++---+-+ | id | score | log_sum | ++---+-+ | 1 |10 | 10 | | 2 | 1 | 8 | ++---+-+ 2 rows in set (0.00 sec) mysql select s.id, s.score, sum(l.log) as log_sum from score s, log l where l.id = s.id group by l.id having s.score != log_sum; ++---+-+ | id | score | log_sum | ++---+-+ | 2 | 1 | 8 | ++---+-+ 1 row in set (0.00 sec) ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query or code?
On Thu, 6 Nov 2003, CPT John W. Holmes wrote: From: Peter Beckman [EMAIL PROTECTED] I have this data: Table Log: appid userid points datetype Table Score: appid userid score I want to verify that the last entry in table log of type x is equal to the sum of the scores in table score for the same appid and userid. Can I do this in SQL easily? My problem is selecting the correct (most recent) row in log in which to match the score. Basically I want a report of AppID, TeamMemberID, log.points, score.score that shows where points != score; Why do you have a score table at all? That's just repeating data when you can always do a SUM query on the log table to get the score... Athough... SQL example: Almost; Here's the hard part: mysql select logid, type, date, ApplicationID, TeamMemberID, Points from log where applicationid=19933 and teammemberid=63 and type=Promotion order by date desc; +---+---+-+---+--++ | logid | type | date| ApplicationID | TeamMemberID | Points | +---+---+-+---+--++ | 2966 | Promotion | 2003-08-14 17:43:22 | 19933 | 63 | 71 | | 2381 | Promotion | 2003-08-01 13:02:56 | 19933 | 63 | 81 | | 2373 | Promotion | 2003-08-01 12:54:20 | 19933 | 63 | 81 | | 2105 | Promotion | 2003-07-31 15:06:55 | 19933 | 63 | 84 | +---+---+-+---+--++ 4 rows in set (0.02 sec) mysql select ApplicationID, TeamMemberID, sum(score) as score from score where Applicationid=19933 and teammemberid=63 group by ApplicationID, TeamMemberID; +---+--+---+ | ApplicationID | TeamMemberID | score | +---+--+---+ | 19933 | 63 |96 | +---+--+---+ 1 row in set (0.01 sec) I want to see the comparison done with the most recent row of type Promotion from the log table: +---+--+---+---+ | ApplicationID | TeamMemberID | log_score | score | +---+--+---+---+ | 19933 | 63 |71 |96 | +---+--+---+---+ Can it be done with a single query? I can do it brilliantly easily in code, but I like the challenge of doing it in SQL. --- Peter Beckman Director of Internet Initiatives North American Managementhttp://www.nambco.com/ 703.683.0292 x239[EMAIL PROTECTED] --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query or code?
[EMAIL PROTECTED] wrote: On Thu, 6 Nov 2003, CPT John W. Holmes wrote: From: Peter Beckman [EMAIL PROTECTED] I have this data: Table Log: appid userid points datetype Table Score: appid userid score I want to verify that the last entry in table log of type x is equal to the sum of the scores in table score for the same appid and userid. Can I do this in SQL easily? My problem is selecting the correct (most recent) row in log in which to match the score. Basically I want a report of AppID, TeamMemberID, log.points, score.score that shows where points != score; Why do you have a score table at all? That's just repeating data when you can always do a SUM query on the log table to get the score... I'd still like an answer to this question. Why is there a need for a separate table with scores? Can it be done with a single query? I can do it brilliantly easily in code, but I like the challenge of doing it in SQL. Without knowing the exact table structure, maybe this'll work? mysql select s.applicationid, s.teammemberid, l.points, sum(s.score), date from score s, log l wher e s.applicationid = l.applicationid group by l.points order by l.date desc limit 1; +---+--++--+-+ | applicationid | teammemberid | points | sum(s.score) | date | +---+--++--+-+ | 19933 | 63 | 71 | 96 | 2003-08-14 17:43:22 | +---+--++--+-+ 1 row in set (0.00 sec) -- ---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 or code?
On Thu, 6 Nov 2003, John W. Holmes wrote: I'd still like an answer to this question. Why is there a need for a separate table with scores? The log is a snapshot in time -- what was the total points at the time of the log entry. The score table is always the accurate current score. Can it be done with a single query? I can do it brilliantly easily in code, but I like the challenge of doing it in SQL. Without knowing the exact table structure, maybe this'll work? mysql select s.applicationid, s.teammemberid, l.points, sum(s.score), date from score s, log l where s.applicationid = l.applicationid group by l.points order by l.date desc limit 1; +---+--++--+-+ | applicationid | teammemberid | points | sum(s.score) | date| +---+--++--+-+ | 19933 | 63 | 71 | 96 | 2003-08-14 17:43:22 | +---+--++--+-+ 1 row in set (0.00 sec) That works for 1 row, but I want 30+ unique appid's and teammemberid's with the most recent date points. Since some 2nd-to-last promotions occurred much later than the last promotion on others, ordering by date doesn't help either. Basically I need a report that says: When comparing the last Promotion log entry on table log, these are the applicationid and teammemberid combinations in which the sum of the items matching in the score table does not equal the points in the selected log entry. Peter --- Peter Beckman Internet Guy [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Error
From: Robert Sossomon [EMAIL PROTECTED] And here is the additem.php file where it check for the info in the field and it is erroring out. It would be rather useful to know the error... $get_iteminfo = select * from GCN_items where 'item_num' LIKE '%$_POST[sel_item_id]%'; Take away the single quotes around your column name. You're asking for a 'string' LIKE 'another string' and not comparing anything in your column at all. You can use backticks around table and column names, though. Maybe that's what you're trying to achieve. $get_iteminfo = select * from GCN_items where `item_num` LIKE '%$_POST[sel_item_id]%'; ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query Error
The errors as it prints are: The query I just ran was: select * from GCN_items where `item_num` = '%fm-a294%' The query I just ran was: Resource id #2 0 -Original Message- From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 11:04 AM To: Robert Sossomon; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Query Error From: Robert Sossomon [EMAIL PROTECTED] And here is the additem.php file where it check for the info in the field and it is erroring out. It would be rather useful to know the error... $get_iteminfo = select * from GCN_items where 'item_num' LIKE '%$_POST[sel_item_id]%'; Take away the single quotes around your column name. You're asking for a 'string' LIKE 'another string' and not comparing anything in your column at all. You can use backticks around table and column names, though. Maybe that's what you're trying to achieve. $get_iteminfo = select * from GCN_items where `item_num` LIKE '%$_POST[sel_item_id]%'; ---John Holmes... -- 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 Error
From: Robert Sossomon [EMAIL PROTECTED] The errors as it prints are: The query I just ran was: select * from GCN_items where `item_num` = '%fm-a294%' The query I just ran was: Resource id #2 0 Those aren't errors; it's just what you asked the script to display. Your query simply isn't returning any rows. You should be using LIKE instead of the equal sign, since I assume you're looking for a pattern. select * from GCN_items where `item_num` LIKE '%fm-a294%' otherwise you're looking for the literal string %fm-a294% ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query Error
That fixed the output to actually find the information, however I'll be danged if it is not adding the items to the database. In fact I am seeing errors with it adding half the stuff to the database from stuff that was working. I rebooted the system. Removed the database, stopped the server. Started the server. Reloaded the database. Refreshed the data. Now I am not seeing stuff added to the system and I have no idea why. According to the scripts everything is working fine. The information is being passed to the pages correctly and even the output on the page showed that the data was correct for insertion, however when I look at the database where the information should be, it is not there. I'm losing my mind! Any thoughts? ~~~ A horse! A horse! My kingdom for a horse! -Original Message- From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 1:31 PM To: Robert Sossomon; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Query Error From: Robert Sossomon [EMAIL PROTECTED] The errors as it prints are: The query I just ran was: select * from GCN_items where `item_num` = '%fm-a294%' The query I just ran was: Resource id #2 0 Those aren't errors; it's just what you asked the script to display. Your query simply isn't returning any rows. You should be using LIKE instead of the equal sign, since I assume you're looking for a pattern. select * from GCN_items where `item_num` LIKE '%fm-a294%' otherwise you're looking for the literal string %fm-a294% ---John Holmes... -- 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 works but can't echo variable :'(
You're using an alias of 'date' instead of DateOfOrder, so that should be reflected in the array key. So: $Date = $OrderDate[date]; -Micah On Sun October 26 2003 4:22 pm, Graeme McLaren wrote: Hey everyone, the following query and code works fine but I can't understand why I can't echo the $Date variable. I guess it doesn't work fine then :( Any ideas ? G :) $TheDate = mysql_query(select DATE_FORMAT(DateOfOrder, '%d, %m, %Y') as date from Orders Where Orders.ID = '$TransID'); while($OrderDate = mysql_fetch_array($TheDate)) { $Date = $OrderDate[DateOfOrder]; } -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query for multiple values
SELECT * FROM table WHERE column IN ( 'this', 'that', 'theother' ) Ignatius _ - Original Message - From: Robbie Staufer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 16, 2003 12:10 AM Subject: [PHP-DB] query for multiple values Hi, Is there a way to construct a mysql query that will select * for multiple values in the same column? For example: SELECT * FROM table WHERE column = 'this' and 'that' and 'theother' Thanks, Robbie -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836 -- 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 Filter in GUI
how are you testing the check boxes?, looks like the the query string is empty because both the tests you are doing in the code below return false. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 22 September 2003 15:01 To: [EMAIL PROTECTED] Subject: [PHP-DB] Query Filter in GUI Good Morning PHP-DB Listers, I have a PHP GUI in which a select box gets populated by a query of a MySQL DB for all CD's by all artists when the page is first opened. I am now trying to implement the same query but with a WHERE clause that filters the returned CD's to be displayed in the same select box according to the artist selected via a group of checkboxes (one per artist). The problem so far is that if I try to implement some flow control (if checbox one is selected, append where artist='hendrix' to the select all CDs query, mysql_query() etc...), I get an error message saying that the query string is empty. So I am looking for some advice about how to implement the logic of having the same select box display the results of different but related queries. For example, $query_1 = SELECT CD_title FROM CDs order by CD_title; $result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs . mysql_error()); while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc... works just fine. But if I try to use some flow control to alter which query statement is assigned to $query_1... if(checkbox one is selected) { $query_1 = SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix' order by CD_title; } elseif(no checkboxes are checked) { $query_1 = SELECT CD_title FROM CDs order by CD_title; } $result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs . mysql_error()); while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc... produces the error message stated above ... that the query string is empty. I've also tried to duplicate the mysql_query($query_1) or die()... within each section of the IF group, but then the select box does not get populated at all. I am using session variables, and I'm certain that the checkboxes are correctly assigning the necessary values to the variables associated with them because I am echo'ing the variables and I can see the values that they have. And when I run the query with the where clause in the MySQL client, it works just fine. I'm guessing I'm missing something fairly simple because this type of functionality must have been implemented before. Thanks for reading the rather long question. David -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query Filter in GUI
From: [EMAIL PROTECTED] I have a PHP GUI in which a select box gets populated by a query of a MySQL DB for all CD's by all artists when the page is first opened. I am now trying to implement the same query but with a WHERE clause that filters the returned CD's to be displayed in the same select box according to the artist selected via a group of checkboxes (one per artist). The problem so far is that if I try to implement some flow control (if checbox one is selected, append where artist='hendrix' to the select all CDs query, mysql_query() etc...), I get an error message saying that the query string is empty. So I am looking for some advice about how to implement the logic of having the same select box display the results of different but related queries. For example, $query_1 = SELECT CD_title FROM CDs order by CD_title; $result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs . mysql_error()); while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc... works just fine. But if I try to use some flow control to alter which query statement is assigned to $query_1... if(checkbox one is selected) { $query_1 = SELECT CD_title FROM CDs WHERE artist_name = 'Hendrix' order by CD_title; } elseif(no checkboxes are checked) { $query_1 = SELECT CD_title FROM CDs order by CD_title; } $result_1 = mysql_query($query_1) or die(Can't select CD_title from CDs . mysql_error()); while($row=mysql_fetch_array($result_1, MYSQL_BOTH)) ..etc... produces the error message stated above ... that the query string is empty. I've also tried to duplicate the mysql_query($query_1) or die()... within each section of the IF group, but then the select box does not get populated at all. In order for you to get that error, neither of your two conditions are TRUE. Since you have pseudo-code, it's hard to tell where the problem is, though.Something in (checkbox one is selected) and (no checkboxes are checked) is not right. Here's an easy way to do this, though, so pay attention. :) Create your checkboxes like this: input type=checkbox name=artist[] value=Hendrix input type=checkbox name=artist[] value=Violent Femmes etc... Notice how they are all named the same and have different value. Now, when processing this form, you'll have a variable $_GET['artist'] that's an array. If a checkbox was checked, then the array will have some elements, otherwise it'll be empty and not set... so use something like this: if(isset($_GET['artist']) is_array($_GET['artist']) !empty($_GET['artist'])) { $artist_list = ' . implode(',',$_GET['artist']) . '; $query = SELECT CD_title FROM CDs WHERE artist_name IN ($artist_list) order by CD_title; } else { $query = SELECT CD_title FROM CDs order by CD_title; } Then run your query and retrieve the results like you are doing now. What this will to is take multiple checkboxes selected by the user (or just one if you want) and make it into a comma separated list to send to the query. So if two checkboxes are selected, you end up with a query such as: WHERE artist_name IN ('Hendrix','Violent Femmes') Hope that helps. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query runs fine on Console but not in PHP
First FreeTDS should have built support for TDS protocol version 8.0. The tds protocol version should also be set to 8.0 in interface file. Details about this is included with the FreeTDS source distribution. -- bob On Mon, 18 Aug 2003, vish.kohli wrote: Thanks for your response. My script is running on Linux. I am trying to retrieve simple data such as text and numbers. I am not sure what the TDS protocol setting should be. What are the typical settings and how can I check it? Would appreciate any tips for debugging. Thanks. - Original Message - From: Robert Twitty [EMAIL PROTECTED] To: vish.kohli [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, August 18, 2003 8:28 AM Subject: Re: [PHP-DB] Query runs fine on Console but not in PHP What platform are you using? Since it appears that the script is crashing, you are probably on Linux or some other UNIX flavor. If this is the case, the TDS protocol setting may not be correct. What type of date are you retrieving with this stored procedure? -- bob On Sun, 17 Aug 2003, vish.kohli wrote: Hi, I am trying to execute a stored procedure in PHP via mssql_query () function. I pass 8 parameters to it. The query runs fine in SQL Query Analyzer but when I run it thru PHP, it behaves strangely. I can get number of rows (mssql_num_rows()) and number of fields (mssql_num_fields()) in PHP, but when I try to execute mssql_fetch_object() or mssql_fetch_array() on the same result identifier, I get a Page could not be displayed (standard internet error page). Please help. Thanks in advance. -- 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query runs fine on Console but not in PHP
Thanks for your response. My script is running on Linux. I am trying to retrieve simple data such as text and numbers. I am not sure what the TDS protocol setting should be. What are the typical settings and how can I check it? Would appreciate any tips for debugging. Thanks. - Original Message - From: Robert Twitty [EMAIL PROTECTED] To: vish.kohli [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, August 18, 2003 8:28 AM Subject: Re: [PHP-DB] Query runs fine on Console but not in PHP What platform are you using? Since it appears that the script is crashing, you are probably on Linux or some other UNIX flavor. If this is the case, the TDS protocol setting may not be correct. What type of date are you retrieving with this stored procedure? -- bob On Sun, 17 Aug 2003, vish.kohli wrote: Hi, I am trying to execute a stored procedure in PHP via mssql_query () function. I pass 8 parameters to it. The query runs fine in SQL Query Analyzer but when I run it thru PHP, it behaves strangely. I can get number of rows (mssql_num_rows()) and number of fields (mssql_num_fields()) in PHP, but when I try to execute mssql_fetch_object() or mssql_fetch_array() on the same result identifier, I get a Page could not be displayed (standard internet error page). Please help. Thanks in advance. -- 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 runs fine on Console but not in PHP
What platform are you using? Since it appears that the script is crashing, you are probably on Linux or some other UNIX flavor. If this is the case, the TDS protocol setting may not be correct. What type of date are you retrieving with this stored procedure? -- bob On Sun, 17 Aug 2003, vish.kohli wrote: Hi, I am trying to execute a stored procedure in PHP via mssql_query () function. I pass 8 parameters to it. The query runs fine in SQL Query Analyzer but when I run it thru PHP, it behaves strangely. I can get number of rows (mssql_num_rows()) and number of fields (mssql_num_fields()) in PHP, but when I try to execute mssql_fetch_object() or mssql_fetch_array() on the same result identifier, I get a Page could not be displayed (standard internet error page). Please help. Thanks in advance. -- 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 Cache not Working? MySQL 4.0.14-standard
From: Matt Babineau [EMAIL PROTECTED] For some reason my MySQL doesn't seem to be caching queries! And you expect PHP to solve this how? Do you want PHP to cache the queries? You must say please... You need to set the four variables mentioned at the following URL in your my.cnf file. http://www.mysql.com/doc/en/Query_Cache_Configuration.html especially the last two that give the size limit for the cache and turn it ON. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query works fine on console but not with PHP - why?
- Original Message - From: Aaron Wolski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 9:59 AM Subject: [PHP-DB] query works fine on console but not with PHP - why? The error I am getting on the browser is: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in http://www.martekbiz.com/KCS/utils.inc on line 52 Anyone have an idea as to what it up? Try: echo mysql_error(); and see what mysql says. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query works fine on console but not with PHP - why? SOLVED
Hi and thanks, Actually.. there was a typo in an if() statement just prior to it that checked to see if a variable was set before proceeding with the query. What a dumb-ass I am. You know you've been working way to long when you can't do simple things. Thanks for your comments! Aaron -Original Message- From: Matt [mailto:[EMAIL PROTECTED] Sent: August 3, 2003 10:11 AM To: Aaron Wolski; [EMAIL PROTECTED] Subject: Re: [PHP-DB] query works fine on console but not with PHP - why? - Original Message - From: Aaron Wolski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 9:59 AM Subject: [PHP-DB] query works fine on console but not with PHP - why? The error I am getting on the browser is: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in http://www.martekbiz.com/KCS/utils.inc on line 52 Anyone have an idea as to what it up? Try: echo mysql_error(); and see what mysql says. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query works fine on console but not with PHP - why?
You have an error in your PHP code, just as the error says. Including only the query, which you've already tested, isn't very helpful. The error message is telling you that the result link identifier used in your fetch statement isn't the same as returned by the query statement OR there was an error returned by the query. It's all in the manual. Doug On Sun, 3 Aug 2003 09:59:42 -0400, Aaron Wolski wrote: Hi guys, I have the following query which returns FINE through the console but not in PHP select t.id, t.colour, t.colourID, t.price, t.type, g.thread_index, g.groupNameUrl FROM kcs_threads t LEFT JOIN kcs_threadgroups g ON t.id = g.thread_index WHERE g.groupNameUrl = '0-500' The error I am getting on the browser is: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in http://www.martekbiz.com/KCS/utils.inc on line 52 Anyone have an idea as to what it up? Thanks! Aaron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query, display and grouping.. one way works but notanother
Aaron Wolski wrote: Hi Guys, Have a multiple select box. I'm trying to group columns and display records under their paired groupings. [snip] What I'd like is to display them like: Manufactuer - Type Manufacturer - Type Manufacturer - Type I tried this code but only seems to group one TYPE and move onto the next manufacturer and group that type.: echo select name=\patternThreads[]\ size=\10\ multiple style=\font-family:monospace;\\n; $query = select t.manufacturer, t.id, t.colour, t.colourID, t.type, p.thread_index FROM kcs_threads t LEFT JOIN kcs_patternthreads p ON t.id = p.thread_index WHERE p.pattern_index = '$id' OR p.pattern_index IS NULL ORDER BY t.manufacturer, t.type; $thread_manufacturer = ''; $thread_types = ''; $result = db_query($query); while($thread = db_fetch($result)) { if($thread_manufacturer != $thread['manufacturer']) { echo option value=\\ class=\adminEditLink\{$thread['manufacturer']}; $thread_manufacturer = $thread['manufacturer']; } if($thread_types != $thread['type']) { echo - {$thread['type']}; $thread_types = $thread['type']; } echo /option\n; } echo /select\n; Any idea's where I am going wrong? Wouldn't it just be: while($thread = db_fetch($result)) { { echo option value=\\ class=\adminEditLink\{$thread['manufacturer']} - {$thread['type']}/option\n; } Although I don't see where you're setting a value for the option in your code. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A 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 and display acting weird...
Probably need: ORDER BY t.manufacturer, t.colourID ---John Holmes... - Original Message - From: Aaron Wolski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 25, 2003 2:58 PM Subject: [PHP-DB] query and display acting weird... Hi Guys, Code: echo select name=\patternThreads[]\ size=\10\ multiple style=\font-family:monospace;\\n; $query = select t.manufacturer, t.id, t.colour, t.colourID, t.type, p.thread_index FROM kcs_threads t LEFT JOIN kcs_patternthreads p ON t.id = p.thread_index WHERE p.pattern_index = '$id' OR p.pattern_index IS NULL ORDER BY t.colourID; $thread_manufacturer = ''; $result = db_query($query); while($thread = db_fetch($result)) { if($thread_manufacturer != $thread['manufacturer']) { echo option value=\\ class=\adminEditLink\{$thread['manufacturer']}/option\n; $thread_manufacturer = $thread['manufacturer']; } } echo /select\n; In my select box I am getting results like: Canterbury Cross Fabrics Zweigart Permin Zweigart I SHOULD be getting: Canterbury Cross Fabrics Zweigart Permin This was once working.. as it should have but today more data was dumped into the table which is causing the second Zweigart to be displayed. If I group the manufacturer in the query then I only one record displayed for the corresponding manufacturers. Any clue how I can make it so that everything is sorted under grouped manufacturers but still get ALL the results for the manufacturer at the same time? Have over 4000 records which was causing a LONG loading time in the browser so John Holmes gave me the above query to replace where I had 3 queries doing the same thing. ANY thoughts would be greatly welcomed! Thanks Aaron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query problem
Am Dienstag, 22. Juli 2003 11:29 schrieb Ron Allen: This is what I have select * from ticket where Type = 'Line' and Closeddate = ' empty space' and have tried the following select * from ticket where Type = 'Line' and Closeddate = 'Null' select * from ticket where Type = 'Line' and Closeddate = 'NULL' use isNULL(Closedate) instead of Closedate = 'NULL' -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Query problem
you can't use COL_NAME = NULL or COL_NAME = 'NULL' and get the desired results , you have to check for IS NULL/ IS NOT NULLSo, change the query to SELECT * FROM TABLE WHERE MY_FIELD IS NULL or, SELECT * FROM TABLE WHERE MY_FIELD IS NOT NULL From my understanding when you check for COL_NAME = 'Null' you're actually checking to see if your field contains a _string_ 'Null' hth jeff Boaz Yahav [EMAIL PROTECTED]To: Ron Allen [EMAIL PROTECTED], [EMAIL PROTECTED] .net.il cc: Subject: RE: [PHP-DB] Query problem 07/22/2003 03:16 PM Assuming you run MySQL, why don't you just remove the ' ? SELECT * FROM MyTable WHERE MyField=NULL Sincerely berber Visit http://www.weberdev.com/ Today!!! To see where PHP might take you tomorrow. Share your code : http://addexample.weberdev.com -Original Message- From: Ron Allen [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 11:29 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Query problem I have done this in the past, but for some reason it isn't working now (maybe a moron). I am trying to select all of the empty or NULL fields in a column. This is what I have select * from ticket where Type = 'Line' and Closeddate = ' empty space' and have tried the following select * from ticket where Type = 'Line' and Closeddate = 'Null' select * from ticket where Type = 'Line' and Closeddate = 'NULL' any clues -- 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 selection
Okay... and? Do you have a question? Are you wanting us to write this for free? Do you want bids on the project? ---John Holmes... - Original Message - From: Ryan Holowaychuk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 16, 2003 2:30 PM Subject: [PHP-DB] query selection I have a databae built, at the moment it has two tables. The first table has 2 fields, an ID and description. This is for a listing of all the different samples that we have at the company. When the user clicks on the description they want, it will open another page that will show all the items that fit that description. So in this case the first page shows: Brochures, business cards, tickets, etc They then click on tickets I want it to show all the tickets that we have done and then I will have a link from there they can click on and show all the jobs in there. but to even make it more complicated I only want it to show the first job for that client based on the job number. (eg. Tickets each client will have a back, front and inside to show) so when they click on tickets they will see the teams we have done, and then from there they can select a team. And then it will open all the examples they want in there, and then if they click on the thumb it would go to a page I have set up with the image at a large size. Thanks Ryan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] query selection
Sorry forgot to state the question. No bids. Looking for help. the first list that is displayed has an ID for each product. But when I click on it, it goes to the next page but does not display the rest of the products that are listed in the second table. $query = SELECT * FROM product_list WHERE productid = id; Id being the description ID that came off the first page. -Original Message- From: CPT John W. Holmes [mailto:[EMAIL PROTECTED] Sent: Monday, June 16, 2003 11:43 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Okay... and? Do you have a question? Are you wanting us to write this for free? Do you want bids on the project? ---John Holmes... - Original Message - From: Ryan Holowaychuk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 16, 2003 2:30 PM Subject: [PHP-DB] query selection I have a databae built, at the moment it has two tables. The first table has 2 fields, an ID and description. This is for a listing of all the different samples that we have at the company. When the user clicks on the description they want, it will open another page that will show all the items that fit that description. So in this case the first page shows: Brochures, business cards, tickets, etc They then click on tickets I want it to show all the tickets that we have done and then I will have a link from there they can click on and show all the jobs in there. but to even make it more complicated I only want it to show the first job for that client based on the job number. (eg. Tickets each client will have a back, front and inside to show) so when they click on tickets they will see the teams we have done, and then from there they can select a team. And then it will open all the examples they want in there, and then if they click on the thumb it would go to a page I have set up with the image at a large size. Thanks Ryan -- 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 selection
the first list that is displayed has an ID for each product. But when I click on it, it goes to the next page but does not display the rest of the products that are listed in the second table. $query = SELECT * FROM product_list WHERE productid = id; Id being the description ID that came off the first page. Well, 'id' should at least be a variable, right? I assume your links look something like this: http://www.yourdomain.com/page.php?id=X where X is the 'id' number you want to pass to the above select statement. If so, then you'd use: $query = SELECT * FROM product_list WHERE productid = {$_GET['id']}; or $query = SELECT * FROM product_list WHERE productid = . $_GET['id']; where $_GET['id'] is a variable containing the value passed in the URL. Make sure you properly validate $_GET['id'] to make sure it's a valid ID (or at least an integer) before you stick it in your SQL statement, also. $id = (int)$_GET['id']; $query = SELECT * FROM product_list WHERE productid = $id; ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Query takes to much time
What database are you using? Also, are all the fields involved in the joins and whare clause properly indexed? Depending on the database you are using, it is possible to determine if the indexed fields are being used. -- bob On Tue, 3 Jun 2003, André Sannerholt wrote: Hi everybody, now I have the following qery that still needs to much time: select distinct Anschriften.ID as aid, Anschriften.PLZ as aplz, Anschriften.STRASSE as astrasse, Anschriften.HNR as ahnr from Anschriften inner join Master on Anschriften.ID=Master.A inner join Master as m on Master.FN=m.FN inner join Personen on m.NN=Personen.ID where Personen.ID = $id It gives me the correct results I hope you can help me to get it faster! André -- 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 string
Awsome! Thanks, That is exactly what I was looking for. Work great now! Leif K-Brooks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] To answer your question, you should urlencode() the team name. However, I would store the user in a cookie or session, and fetch the team name on each page load. Otherwise, they can easily change which team they're using. Ian Fingold wrote: Sorry I don't think I explained very well... heres my situation... I have a mysql database set up... I have a user table with 2 fields...team_name and fant_week. What I want to do is, depending on who is logged in, I want my link to change the value of 'team' to the logged in members team name, like wise for the week. For example.. if gorno is logged in and his team is called fun team the link will reflect his team and look like this... fant_stnd3.php?week=1team=fun team or if say... billbo is logged in and his team is called silly team the link will look like this fant_stnd3.php?week=1team=silly team but again, my problem is that it's cutting off the team value when there is a space in the string.. G|rhan Vzen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Sun, 2003-06-01 at 22:26, Ian Fingold wrote: I'm trying to build a query string for one of my links.. for example... fant_stnd3.php?week=1team=fun%20team I need to be able to grab the values of 'week' and 'team' from an array, I can do that no problem, but when the code runs it cuts off the value if there is a space in the string. so instead of putting fun team in the query string it cuts it off and just puts.. fun So my question, is there a function or any way to prevent this from happening? thanks. Hi Ian, I have no idea what you are trying to do.. Well if you are getting those values from a URL, $week and $team variables in your php script should have the values in them.. In later versions of PHP, they will be _POST['week'] and _POST['team'] or _GET['week'] or _GET['team'] depending on which method is used.. You don't need to parse the URL and run them through an array.. :) Gurhan -- The above message is encrypted with double rot13 encoding. Any unauthorized attempt to decrypt it will be prosecuted to the full extent of the law. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query string
On Sun, 2003-06-01 at 22:26, Ian Fingold wrote: I'm trying to build a query string for one of my links.. for example... fant_stnd3.php?week=1team=fun%20team I need to be able to grab the values of 'week' and 'team' from an array, I can do that no problem, but when the code runs it cuts off the value if there is a space in the string. so instead of putting fun team in the query string it cuts it off and just puts.. fun So my question, is there a function or any way to prevent this from happening? thanks. Hi Ian, I have no idea what you are trying to do.. Well if you are getting those values from a URL, $week and $team variables in your php script should have the values in them.. In later versions of PHP, they will be _POST['week'] and _POST['team'] or _GET['week'] or _GET['team'] depending on which method is used.. You don't need to parse the URL and run them through an array.. :) Gurhan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query string
Sorry I don't think I explained very well... heres my situation... I have a mysql database set up... I have a user table with 2 fields...team_name and fant_week. What I want to do is, depending on who is logged in, I want my link to change the value of 'team' to the logged in members team name, like wise for the week. For example.. if gorno is logged in and his team is called fun team the link will reflect his team and look like this... fant_stnd3.php?week=1team=fun team or if say... billbo is logged in and his team is called silly team the link will look like this fant_stnd3.php?week=1team=silly team but again, my problem is that it's cutting off the team value when there is a space in the string.. Gürhan Özen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Sun, 2003-06-01 at 22:26, Ian Fingold wrote: I'm trying to build a query string for one of my links.. for example... fant_stnd3.php?week=1team=fun%20team I need to be able to grab the values of 'week' and 'team' from an array, I can do that no problem, but when the code runs it cuts off the value if there is a space in the string. so instead of putting fun team in the query string it cuts it off and just puts.. fun So my question, is there a function or any way to prevent this from happening? thanks. Hi Ian, I have no idea what you are trying to do.. Well if you are getting those values from a URL, $week and $team variables in your php script should have the values in them.. In later versions of PHP, they will be _POST['week'] and _POST['team'] or _GET['week'] or _GET['team'] depending on which method is used.. You don't need to parse the URL and run them through an array.. :) Gurhan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query string
Check out http://www.php.net/manual/en/ref.url.php Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Ian Fingold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, 01 June, 2003 23:20 Subject: Re: [PHP-DB] query string Sorry I don't think I explained very well... heres my situation... I have a mysql database set up... I have a user table with 2 fields...team_name and fant_week. What I want to do is, depending on who is logged in, I want my link to change the value of 'team' to the logged in members team name, like wise for the week. For example.. if gorno is logged in and his team is called fun team the link will reflect his team and look like this... fant_stnd3.php?week=1team=fun team or if say... billbo is logged in and his team is called silly team the link will look like this fant_stnd3.php?week=1team=silly team but again, my problem is that it's cutting off the team value when there is a space in the string.. Grhan zen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Sun, 2003-06-01 at 22:26, Ian Fingold wrote: I'm trying to build a query string for one of my links.. for example... fant_stnd3.php?week=1team=fun%20team I need to be able to grab the values of 'week' and 'team' from an array, I can do that no problem, but when the code runs it cuts off the value if there is a space in the string. so instead of putting fun team in the query string it cuts it off and just puts.. fun So my question, is there a function or any way to prevent this from happening? thanks. Hi Ian, I have no idea what you are trying to do.. Well if you are getting those values from a URL, $week and $team variables in your php script should have the values in them.. In later versions of PHP, they will be _POST['week'] and _POST['team'] or _GET['week'] or _GET['team'] depending on which method is used.. You don't need to parse the URL and run them through an array.. :) Gurhan -- 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 string
On Sun, 2003-06-01 at 23:20, Ian Fingold wrote: Sorry I don't think I explained very well... heres my situation... I have a mysql database set up... I have a user table with 2 fields...team_name and fant_week. What I want to do is, depending on who is logged in, I want my link to change the value of 'team' to the logged in members team name, like wise for the week. For example.. if gorno is logged in and his team is called fun team the link will reflect his team and look like this... fant_stnd3.php?week=1team=fun team or if say... billbo is logged in and his team is called silly team the link will look like this fant_stnd3.php?week=1team=silly team but again, my problem is that it's cutting off the team value when there is a space in the string.. Ok.. I can't say anything, unless i know where and how it is being cut off.. From that scenario you said.. You should probably query the week name and team name from the database and then redirect the browser to the location.. so something like.. $query= select weekname, teamname from tablename where username=.($username). limit 1; $result=mysql_query($query); $row=mysql_fetch_array($result) header( Location: http://servername/fant_stnd3.php?;.($row[weekname]).team=.($row[teamname]).); Of course, you will need to replace servername, tablename and column names with appropriate values.. I hope this helps.. Gurhan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php