Re: [PHP-DB] Yet more strings
Gary Thanks for the advice. I had checked the string length to make sure there were no "hidden" spaces (which there aren't in the specific field I am selecting) and have now tried trim() but to no avail. Still fiddling with it! Regards Mick Lloyd [EMAIL PROTECTED] Tel: +44 (0)1684 560224 - Original Message - From: Gary Huntress <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, March 21, 2001 12:45 PM Subject: Re: [PHP-DB] Yet more strings > When I have exact string matches fail where I don't expect them, and using a > LIKE fixes it, the very first thing I check for is embedded whitespace in > the data. "foo " and "foo" and " foo " are not the same. > > If this is the case then IMHO the best solution is to fix the data in the > database, but barring that you can probably use the TRIM() function in your > where clause. (note: the MySQL docs show using trim in the output, not in > the where clause so I won't swear that its possible, but it should be) > > Regards, > > Gary > > ""Mick Lloyd"" <[EMAIL PROTECTED]> wrote in message > 00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer">news:00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer... > > CC's suggestion of using mysql_num_rows helped to clarify the problem > rather > > than find a solution! The problem is with mysql not PHP. When I use: > > > > $resultp = mysql_query("select Primaryid from primarycodes where Code = > > '".$row[Primaryexpertise]."'") or die (mysql_error()); > > > > it doesn't return a resource id#, ie the query fails. mysql_num_rows > returns > > 0. > > > > Using: > > > > $resultp = mysql_query("select Primaryid from primarycodes where Code LIKE > > '%$row[Primaryexpertise]%'") or die (mysql_error()); > > > > it works, mysql_num_rows returns 1. > > > > I have also checked the string length of $row[Primaryexpertise] (the value > > of which is Biology for example) and it shows that there are no "hidden" > > characters in the field value (ie strlen returns 7). > > > > Running all this at the mysql shell (is that the word?) results in the > same > > errors. > > > > The row values are Primaryid (8) and Code (Biology). > > > > mysql>select Primaryid,Code from primarycodes where Code like '%Biology%'; > > \\returns the correct values > > > > mysql>select Primaryid,Code from primarycodes where Primaryid = 8; > \\returns > > the correct values > > > > mysql>select Primaryid,Code from primarycodes where Code = 'Biology'; > > \\returns Empty set > > > > I'm no expert (obviously) but something seems odd here. > > Regards > > > > Mick Lloyd > > [EMAIL PROTECTED] > > Tel: +44 (0)1684 560224 > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: [EMAIL PROTECTED] > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Yet more strings
i've had similar problems with mysql. postgres seems happy with having quotes around data pulled back as associative arrays like $data = $result["userid"]; and from using mysql it sometimes likes it that way and sometimes without. the syntax for the string is right as long as the string follows the same conventions throughout. like: $query ="select date from date-table where date like '".$date."'"; if primaryexpertise is text does it need the extra ' ? i think this may be clodser to the truth as %primaryexpertise% would return a hit if there were extra chars at the front and end of primaryexpertise Steve > -Original Message- > From: Johannes Janson [mailto:[EMAIL PROTECTED]] > Sent: 21 March 2001 12:00 > To: [EMAIL PROTECTED] > Subject: Re: [PHP-DB] Yet more strings > > > Hi, > > I'm not quite sure but try to play a bit with the > '".$row[...]."' part. > that looks too much. leave out the "" and/or the period. > > good luck > Johannes > > > ""Mick Lloyd"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > 00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer">news:00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer... > > CC's suggestion of using mysql_num_rows helped to clarify > the problem > rather > > than find a solution! The problem is with mysql not PHP. When I use: > > > > $resultp = mysql_query("select Primaryid from primarycodes > where Code = > > '".$row[Primaryexpertise]."'") or die (mysql_error()); > > > > it doesn't return a resource id#, ie the query fails. mysql_num_rows > returns > > 0. > > > > Using: > > > > $resultp = mysql_query("select Primaryid from primarycodes > where Code LIKE > > '%$row[Primaryexpertise]%'") or die (mysql_error()); > > > > it works, mysql_num_rows returns 1. > > > > I have also checked the string length of > $row[Primaryexpertise] (the value > > of which is Biology for example) and it shows that there > are no "hidden" > > characters in the field value (ie strlen returns 7). > > > > Running all this at the mysql shell (is that the word?) > results in the > same > > errors. > > > > The row values are Primaryid (8) and Code (Biology). > > > > mysql>select Primaryid,Code from primarycodes where Code > like '%Biology%'; > > \\returns the correct values > > > > mysql>select Primaryid,Code from primarycodes where Primaryid = 8; > \\returns > > the correct values > > > > mysql>select Primaryid,Code from primarycodes where Code = > 'Biology'; > > \\returns Empty set > > > > I'm no expert (obviously) but something seems odd here. > > Regards > > > > Mick Lloyd > > [EMAIL PROTECTED] > > Tel: +44 (0)1684 560224 > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > To contact the list administrators, e-mail: > [EMAIL PROTECTED] > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: > [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Yet more strings
When I have exact string matches fail where I don't expect them, and using a LIKE fixes it, the very first thing I check for is embedded whitespace in the data. "foo " and "foo" and " foo " are not the same. If this is the case then IMHO the best solution is to fix the data in the database, but barring that you can probably use the TRIM() function in your where clause. (note: the MySQL docs show using trim in the output, not in the where clause so I won't swear that its possible, but it should be) Regards, Gary ""Mick Lloyd"" <[EMAIL PROTECTED]> wrote in message 00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer">news:00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer... > CC's suggestion of using mysql_num_rows helped to clarify the problem rather > than find a solution! The problem is with mysql not PHP. When I use: > > $resultp = mysql_query("select Primaryid from primarycodes where Code = > '".$row[Primaryexpertise]."'") or die (mysql_error()); > > it doesn't return a resource id#, ie the query fails. mysql_num_rows returns > 0. > > Using: > > $resultp = mysql_query("select Primaryid from primarycodes where Code LIKE > '%$row[Primaryexpertise]%'") or die (mysql_error()); > > it works, mysql_num_rows returns 1. > > I have also checked the string length of $row[Primaryexpertise] (the value > of which is Biology for example) and it shows that there are no "hidden" > characters in the field value (ie strlen returns 7). > > Running all this at the mysql shell (is that the word?) results in the same > errors. > > The row values are Primaryid (8) and Code (Biology). > > mysql>select Primaryid,Code from primarycodes where Code like '%Biology%'; > \\returns the correct values > > mysql>select Primaryid,Code from primarycodes where Primaryid = 8; \\returns > the correct values > > mysql>select Primaryid,Code from primarycodes where Code = 'Biology'; > \\returns Empty set > > I'm no expert (obviously) but something seems odd here. > Regards > > Mick Lloyd > [EMAIL PROTECTED] > Tel: +44 (0)1684 560224 > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Yet more strings
Hi, I'm not quite sure but try to play a bit with the '".$row[...]."' part. that looks too much. leave out the "" and/or the period. good luck Johannes ""Mick Lloyd"" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer">news:00c001c0b1ea$54a5f5c0$5c0083ca@oemcomputer... > CC's suggestion of using mysql_num_rows helped to clarify the problem rather > than find a solution! The problem is with mysql not PHP. When I use: > > $resultp = mysql_query("select Primaryid from primarycodes where Code = > '".$row[Primaryexpertise]."'") or die (mysql_error()); > > it doesn't return a resource id#, ie the query fails. mysql_num_rows returns > 0. > > Using: > > $resultp = mysql_query("select Primaryid from primarycodes where Code LIKE > '%$row[Primaryexpertise]%'") or die (mysql_error()); > > it works, mysql_num_rows returns 1. > > I have also checked the string length of $row[Primaryexpertise] (the value > of which is Biology for example) and it shows that there are no "hidden" > characters in the field value (ie strlen returns 7). > > Running all this at the mysql shell (is that the word?) results in the same > errors. > > The row values are Primaryid (8) and Code (Biology). > > mysql>select Primaryid,Code from primarycodes where Code like '%Biology%'; > \\returns the correct values > > mysql>select Primaryid,Code from primarycodes where Primaryid = 8; \\returns > the correct values > > mysql>select Primaryid,Code from primarycodes where Code = 'Biology'; > \\returns Empty set > > I'm no expert (obviously) but something seems odd here. > Regards > > Mick Lloyd > [EMAIL PROTECTED] > Tel: +44 (0)1684 560224 > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]