visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Languages :: PHP :: Using a like command in a MySQL query
By: RICHARDH U.S.A.  Date: 10/05/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
This one is a puzzle I think.
I am using the following block of code to extract data from a MySQL table.

$result = mysql_query("SELECT * FROM schools where name like '$name%'");
print mysql_error();
$a=mysql_num_rows($result);
if ($a>0) {
for ($i=0;$i<$a;$i++) {
$name[$i]=mysql_result($result,$i,"name");
$post[$i]=mysql_result($result,$i,"postcode");
$code[$i]=mysql_result($result,$i,"schoolcode");
$count++;
}
}

The code works, but it is only the first letter of the name field that gets placed into the array $name[$i] and later printed.

If I do a search looking for Apple and there is a field containing 'Apple Pie' I just get the letter 'A'.

Any ideas would be much apprecaited.

Thanks.
By: VGR Date: 10/05/2003 04:40:00 English  Type : Answer
just to check, could you try this ?

$result = mysql_query("SELECT * FROM schools where name like '$name%'") or die(mysql_error());
$a=mysql_num_rows($result);
if ($a>0) {
$i=0;
while ($res=mysql_fetch_array($result)) {
$name[$i]=$res['name'];
$post[$i]=$res['postcode'];
$code[$i]=$res['schoolcode'];
$i++;
} // while results
} // if results

if it still fails, then do this :
$result = mysql_query("SELECT * FROM schools where name like '$name%'") or die(mysql_error());
$name=array();
$post=array();
$code=array();
$a=mysql_num_rows($result);
if ($a>0) {
$i=0;
while ($res=mysql_fetch_array($result)) {
$name[$i]=$res['name'];
$post[$i]=$res['postcode'];
$code[$i]=$res['schoolcode'];
$i++;
} // while results
} // if results
By: darkphoenix Date: 10/05/2003 09:01:00 English  Type : Comment
These two lines of your code concern me:

$result = mysql_query("SELECT * FROM schools where name like '$name%'");
:
:
$name[$i]=mysql_result($result,$i,"name");

Can you have a STRING variable *AND* an ARRAY variable both called $name? (I know PERL would probably allow it, but I'm not sure about PHP off-hand.) If not, isn't $name[$i] referring to a single character subscript of the string $name, as it would in C?

In other words, change

$name[$i]=mysql_result($result,$i,"name");

to

$names[$i]=mysql_result($result,$i,"name");

and modify the rest of your code to suit.
By: darkphoenix Date: 10/05/2003 09:02:00 English  Type : Comment
(or VGR's second version, redeclaring $name as an array, would probably work too!)
By: TheWebMonster Date: 10/05/2003 13:51:00 English  Type : Comment
Try this code. Tested it, it works.
(I was going for functionality not looks).
Adjust the rest of the code to work with the arrays.

TheWebMonster

<?php
$name = array();
$post = array();
$code = array();

$result = mysql_query("SELECT * FROM schools where name like '$name%'");
print mysql_error();
$a=mysql_num_rows($result);
if ($a>0) {
for ($i=0;$i < $a;$i++) {

array_push($name,mysql_result($result,$i,"name"));
array_push($post,mysql_result($result,$i,"postcode");
array_push($code,mysql_result($result,$i,"schoolcode");

$count++;
}
}

//First example for getting values from array
foreach ($name as $key => $value) {
echo "Key: $key; Value: $value
\n";
}

// second example for getting values from array
echo $name[0];

//third example for getting values from array
$num = sizeof($name);
for($x=0;$x < $num;$x++){
echo $name[$x]."
";
}

?>
By: VGR Date: 10/05/2003 15:33:00 English  Type : Comment
the third example is wrong, it's not "sizeof" it's "count"
By: TheWebMonster Date: 10/05/2003 15:51:00 English  Type : Comment
tryed it in PHP 4.0.1 it worked!
count() may return 0 for a variable that has been initialized with an empty array.
By: VGR Date: 10/05/2003 16:12:00 English  Type : Comment
heh? It's normal...

sizeof(array) has posed problems to some people in the past here on EEE ;-)

count(array) counts elements, from 0 to ... N-1 and it's perfectly working.

Final note for Asker : even if a string is an array of Char (normal), count($string) won't return the same value as strlen($string)
By: RICHARDH Date: 11/05/2003 18:36:00 English  Type : Comment
Thanks for all the help guys. Lots for me to think about and play with.

Can anyone tell me the advantage of using echo instead of print. I see that it has been used in one of the examples shown above.

Richard.H
By: VGR Date: 11/05/2003 18:44:00 English  Type : Comment
I don't know and I've the "flemme" to go into doc to see ;-)

I use echo because it's neat and clear, and enables this :
echo <<<EOS
HTML code with $phpvariable in it
more HTML
EOS;
By: blehda Date: 11/05/2003 20:09:00 English  Type : Comment
did you tried to set your like variable into 2 percents ?
...like '%$variable%'.. for example
By: RICHARDH Date: 11/05/2003 20:58:00 English  Type : Comment
Another question....

I have never used the die at the end of a SQL command before. What is the purpose of this line.

Thanks for the help once again.

Richard.H
By: TheWebMonster Date: 11/05/2003 21:53:00 English  Type : Comment
The simple way to remember:

you can specify an error code if your SQL doesn't execute properly.

$sql = "SELECT * FROM MyTABLE";
$res = mysql_query($sql) or die("problem with MyTABLE query");

If for some reason the SQL returns an error, insted of displaying a general SQL error message it will display "problem with MyTABLE query".

Now you know where to start looking for a bug.
By: VGR Date: 11/05/2003 23:09:00 English  Type : Comment
the advantage is that the program will HALT on error.
So you don't have to test the validity of $res afterwards, for example.
By: blehda Date: 11/05/2003 23:11:00 English  Type : Comment
the die function will stop the processing of your code and output an error message if there is any given.
...die(mysql_error()); give you the correct error message of your sql db out. (that makes searching your error in the query much easier)
By: blehda Date: 11/05/2003 23:13:00 English  Type : Comment
hehe shit, vgr matee 2 mins before :)))
By: VGR Date: 11/05/2003 23:17:00 English  Type : Comment
man, take off your boxing gauntlets while you type :D
By: blehda Date: 11/05/2003 23:49:00 English  Type : Comment
hahahahha :))))

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 370.493890 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page