[SOLVED] Message: Query was empty

[StanLytle]

I need help again.  I'm trying to search a table named `Photos`, in a field named `PhotoDate`, for any photos taken on a specific month and day, regardless of year.  I need a wildcard, or some method of ignoring the year.  The field `PhotoDate` is in the format "2007-04-17" of type varchar(10).  Of all of my attempts, this one doesn't return any mySQL errors, but it comes up blank with the message "Query was empty".  I would like to have the query return the values contained in fields `PhotoID`, `City`, `State`, and `PhotoDate`.

 

<?

$sql = "SELECT * FROM `Photos` WHERE `PhotoDate` LIKE CONVERT(_utf8 \'%%%%-04-17\' USING latin1) COLLATE latin1_swedish_ci";

$result = mysql_query($query) or die(mysql_error());

while(list($PhotoID) = mysql_fetch_row($result))

{

  echo "$PhotoID, $City, $State, $PhotoDate<br>";

}

?>

 

Can someone please tell me what I am doing wrong?

 

Thanks,

Stan

 

[maxic0]

Change

 

$result = mysql_query($query) or die(mysql_error());

 

To

 

$result = mysql_query($sql) or die(mysql_error());

[StanLytle]

I made the changes, but that got me back to the mySQL error message:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'%%%%-04-17\' USING latin1) COLLATE latin1_swedish_ci' at line 1.

 

Stan

 

[maxic0]

what do you want "CONVERT(_utf8 \'%%%%-04-17\' USING latin1) COLLATE latin1_swedish_ci" to do?

 

I havent much experiance in PHP and havent seen this before lol.

[StanLytle]

I got that from doing a manual search via phpMyAdmin, where it said, "Or Do a "query by example" (wildcard: "%")".  The search returned the SQL query that said:

 

SELECT *

FROM `Photos`

WHERE `PhotoDate` LIKE CONVERT( _utf8 '%%%%-04-17'

USING latin1 )

COLLATE latin1_swedish_ci

LIMIT 0 , 30

 

When I clicked generate PHP code, I got:

$sql = 'SELECT * FROM `Photos` WHERE `PhotoDate` LIKE CONVERT(_utf8 \'%%%%-04-17\' USING latin1) COLLATE latin1_swedish_ci';

 

Stan

 

[MadTechie]

only use 1 %

 

ie

 

SELECT * FROM TABLE WHERE NAME LIKE "%Techie%"

[StanLytle]

Using on one % didn't make any difference, same error message, only it has just one % in it.

 

Stan

 

[MadTechie]

Why are you converting on the fly anyways ?

[StanLytle]

re: "Why are you converting on the fly anyways ?" 

 

Because I don't know any better.

 

I did finally get it to work, turns out that the problem was the \ in the condition:

LIKE CONVERT(_utf8 \'%%%%-04-17\' USING latin1)

 

With some additions, here's what works now:

 

<?

echo "<table border>";

echo "<tr><th>Search</th><th>PhotoID</th><th>FileName</th><th>City</th><th>State</th><th>Date</th></tr>";

$query = "SELECT * FROM `Photos` WHERE `PhotoDate` LIKE CONVERT(_utf8 '%%%%-04-17' USING latin1) COLLATE latin1_swedish_ci ORDER BY PhotoDate";

$result = mysql_query($query) or die(mysql_error());

while(list($PhotoID, $FileName, $RosterID, $UserID, $City, $State, $PhotoDate, $PhotoAdded, $Remarks, $Views, $Active, $Resubmit) = mysql_fetch_row($result))

{

echo "<tr>";

echo "<td><a href='PhotoDetails.php?PhotoID=$PhotoID'>Search</a></td>";

echo "<td>$PhotoID</td>";

echo "<td>$FileName</td>";

echo "<td>$City</td>";

echo "<td>$State</td>";

echo "<td>$PhotoDate</td>";

  echo "</tr>";

}

echo "</table>";

?>

 

If there is a better way, I'm open to it.

 

Thanks for the help,

Stan