Help

[idnoble]

Hello
I am trying to create a script that use the variable passed to it from an HTML form to search 2 tables in my database and display the results from both tables, i have created the script but each time i run i always get this error message:

Parse error: parse error in /var/www/html/search.php on line 545

I think its a problem with the query PHP is passing to mysql, i've looked through the code but can't figure it our, please help 🙁 🙁 , My code is below

 <?php
//Create short variable names
$search = $HTTP_POST_VARS['search'];
$search= trim($search);
if (!$search)
{
echo '<p style= "font-size: 15pt color: red"><strong>You have not specified a search term. Please go back and try again.</strong></p>';
exit;
}
$search = addslashes($search);
$session = mysql_connect('localhost', '', '') or die(mysql_error());
if (!$session)
{
echo 'Error: Could not connect to database. Please try again later.';
exit;
}

mysql_select_db('xplosive')or die(mysql_error());
$query = "select * 
from movies, music
where movies.Title like '%".$search."%'", && music.Title like '%".$search.'%";
$result = mysql_query($query) or die(mysql_error()); 

$num_results = mysql_num_rows($result);

echo '<p style= "font-size: 10pt"><strong>Search hits: '.$num_results.'></strong></p>';
for ($i=0; $i <$num_results; $i++)
{
 $row = mysql_fetch_array($result);
echo '<p style= "font-size:14pt; color: blue"><strong>'. ($i+1). ':'; 
echo htmlspecialchars (stripslashes($row['Title']));
 echo '<p style= "font-size: 12pt; color: black"<br />Genre: ';
 echo stripslashes($row['Genre']);
 echo '<br/> Synopsis: ';
 echo stripslashes ($row['Synopsis']);
 echo'<br> Price: ';
 echo stripslashes ($row['Price']);
 echo '<br> Cat Number: ';
 echo stripslashes ($row['Cat_No']);
 echo '</p>';

[nemonoman]

I think this is the problem: Hard to see, so I'll do it in steps:

$query = "select *
from movies, music
where movies.Title like '%".$search."%'", && music.Title like '%".$search.'%";

Specifically:

where movies.Title like '%".$search."%'", && music.Title

More specifically:

$search."%'" <<<< that last double quote right there

OOPS found another problem:

like '%".$search.'%"; <<needs better quotes to wit:

like '%".$search."%'"; << this should work

also I'm not sure that && works in most SQL; prefer AND

I think this will work:

$query = "select *
from movies, music
where movies.Title like '%".$search."%',
AND music.Title like '%".$search."%'";

ALSO DON'T FORGET: You've done a join without any join criteria so you're going to get a ton of useless data (a cartesian product).

You need to add join criteria, that would look something like

AND movies.MusicID=Music.ID

using whatever foreign key connects these 2 tables.

PS: You don't really need to concat that query string using " . "

You can just quote it:

$query = "select *
from movies, music
where movies.Title like '%$search%',
AND music.Title like '%$search%'";

[idnoble]

Thank you for your help, I have corrected the error, but unfortunstely, i'm having Mysql error now, the error is:

You have an error in your SQL syntax near ' AND music.Title like '%matrix%'' at line 3

But the query seems alright to me.

[nemonoman]

echo the full query you are sending to the database:

$query = "select *
from movies, music
where movies.Title like '%$search%',
AND music.Title like '%$search%'";

echo "<P>$query<P>;
$result = mysql_query($query) or die(mysql_error());

[idnoble]

Thank you, it still doesn't work but i tried to refine the code for the sql query and changed it to this

$query = "select *
from movies, music
where movies.Title like '%$search%',
OR music.Title like '%$search%'";

I also tried AND, it came back as search hit 0, meaning it didn't find anything like the search variable in the database, but this is wrong as i have data in my movies table but my music table is empty, at least its supposed to retrieve the data in the movies table.

[nemonoman]

the 0 return could be because your SELECT statement is trying to JOIN to a table with no rows.

I am beginning to think that you don't know what you're doing with your multi-table select...until you do, you will get confusing results (like no rows when you expect some).

Until you've had time to study up on Joins, may I suggest that you stick to querying one table at a time?

$query = "SELECT *
FROM movies
WHERE movies.Title LIKE '%$search%'";

$query = "SELECT *
FROM music
WHERE music .Title LIKE '%$search%'";