SQL, WHERE, and LIKE

[sdlynx]

Hi all,

I'm working on a script that will search through a database and retrieve all entries where the word sdlynx is written anywhere in the story field or comments field, and here is my code:

SELECT * FROM stories WHERE (story LIKE '%sdlynx%' OR comments LIKE '%sdlynx%') ORDER BY dateTime DESC LIMIT 0,15

The code runs perfect in PHPMyAdmin's SQL query box (returning around 15 rows), but when I use mysql_query() it returns 0 rows. Help?

SDLynx

[-grubby]

Well, just to be clear here, you're using PHP, right?

[sdlynx]

Well, just to be clear here, you're using PHP, right?

Yes, PHP.

[Elfy]

moved to programming

[Exershio]

show us the full code for the query bit

edit: be sure you're calling mysql_fetch_assoc(); you can also use mysql_fetch_row(), but I prefer assoc so you can reference the results by field name instead of $result[0] and $result[1], etc

example:

$query = mysql_query("blah blah");
$result = mysql_fetch_assoc($query);

echo $result['field_name'];

[januzi]

You can always put


echo $query.' '.mysql_error() ;

after the line with the mysql_query.

[sdlynx]

actually I have been using:


$query = "SELECT * FROM stories WHERE (story LIKE '%sdlynx%' OR comments LIKE '%sdlynx%') ORDER BY dateTime DESC LIMIT 0,15";
$result = mysql_query($query, $con /* connection */) or die(mysql_error());
while($row = mysql_fetch_array($result) {
echo $row[story];
}

[OpenGuard]

$query = mysql_query("SELECT * FROM stories WHERE (story LIKE '%sdlynx%' OR comments LIKE '%sdlynx%') ORDER BY dateTime DESC LIMIT 0,15") or die(mysql_error());
$entries = mysql_num_rows($query);

echo "Query returned $entries entries.";What's the output ?

[Tony Flury]

Could it be something simple like some database engines don't understand the LIMIT clause ?

[sdlynx]

Could it be something simple like some database engines don't understand the LIMIT clause ?

Doubtful, considering I am using it elsewhere on the site just fine.



$query = mysql_query("SELECT * FROM stories WHERE (story LIKE '%sdlynx%' OR comments LIKE '%sdlynx%') ORDER BY dateTime DESC LIMIT 0,15") or die(mysql_error());
$entries = mysql_num_rows($query);

echo "Query returned $entries entries.";What's the output ?

returns 15 entries...., but when I try to fetch them it returns 0 entries....

EDIT: Oh gosh, I feel like an idiot. Turns out I was editing the wrong part of the code. Thanks for the help though guys, it works now.

[OpenGuard]

$query = mysql_query("SELECT * FROM stories WHERE (story LIKE '%sdlynx%' OR comments LIKE '%sdlynx%') ORDER BY dateTime DESC LIMIT 0,15") or die(mysql_error());

while ($data = mysql_fetch_array($query)) {
echo $data['story'] . "<hr>";
}