[RESOLVED] Search query

boombanguk

How would I get all of the fields data from a search result? as right now the code i'm using seems to only give me the field that it found the search term in.

This is the code i'm using

$query = mysql_query("SELECT title, description FROM $table WHERE title like '%$word%' OR description LIKE '%$word%' ORDER BY viewed DESC LIMIT $startRow,$endRow");

$query2 = mysql_num_rows($query);
if($query2<=0)
{
return "&error=" . mysql_error();
} else {
$counter = 0;
while ($row = mysql_fetch_array($query)){
$counter ++;
echo "&title"."$counter=".$row['title'];
echo "&description"."$counter=".$row['description'];
echo "&url"."$counter=".$row['link'];
echo "&viewed"."$counter=".$row['viewed'];
echo "&date"."$counter=".$row['date'];
echo "&id"."$counter=".$row['index'];
}

$title is returned correctly, but all the other fields come back as error msgs something like "undefined index xxx" etc.

Any ideas?
boombanguk

Piranha

SQL select queries work like this:

SELECT first_field_you_want_returned, second_field, third_field and so on
FROM some_table
WHERE something

boombanguk

ive changed the select query to

$query = mysql_query("SELECT title, description, link, viewed, date, index FROM $table WHERE title like '%$word%' OR description LIKE '%$word%' ORDER BY date DESC LIMIT $startRow,$endRow");}

but now it says I have a syntax error?

Piranha

To test SQL queries you should do like this:

$sql = "SELECT something FROM somewhere WHERE something";
$query = mysql_query($sql) or die("Error: " . mysql_error() . "<br />Query: " . $sql);

Then you will get possible errors in the query. You can also echo $sql and try it in phpmyadmin to see if it is your query or PHP.

boombanguk

ok thanks, but does my query look correct? when I just had title,description it worked fine, but now i've added the others it gives me that syntax error.

Piranha

I can't see anything strange with it. But then I don't know the table structure, so I would not be able to spot a typo or a missing column. With the code I posted you can see where the error is, it is typically just before the code displayed.

boombanguk

it seems to be that fact that I'm using index in the query that is causing the error, index is my primary key (which I also auto increment) can I not use index in that kind of select query?

nemonoman

index is a reserved word, and is causing your query to break.

MySQL is casual about naming columns with reserved words (like 'timestamp'), but you may find it necessary to use slant quotes ( `) in the query when the column name is reserved. Typical examples: timestamp, session, if, etc., used as column names require slant quotes when being queried

mysql_query("SELECT index FROM $table ...

rukia

Why don't you use = SELECT * FROM '$table'... instead of inserting all the field name.

boombanguk

thanks everyone, thats sorted it! 🙂

bradgrafelman

rukia wrote:
Why don't you use = SELECT FROM '$table'... instead of inserting all the field name.

Because "SELECT " is the least scalable (and most wasteful, usually) type of SELECT query and should therefore never be used in favor of listing only the columns you intend to use.