PHP form returns 0 records unless ONLY 1 character entered? 1

[Nina2001]

Has anyone encountered a problem like this?

I have a search form in PHP that will only show valid results if the user enters one character in the input fields.

For example: If I search for "Wild" in the songtitle, I get zero results. But if I search for "w" in the songtitle fields I get thousands. This is a new MySQL table. Did I miss something in my configuration? The fact that it works with just the one character makes me think my PHP is working fine. Here's the link:

http://www.pianomandj.com/karaokesearch.php

the result page is:

http://www.pianomandj.com/ksongsearch_action.php

Thanks!

 

[sleipnir214]

It's impossible to say without knowing exactly how your script is performing the search.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[fireburner69]

Can you post the query you make!

 

[Nina2001]

HTML for Form:

<form action=ksongsearch_action.php name="" method=get>
<input type="hidden" name="offset" value="0">
Song Title:
<input name="songtitle" type="text" size="30">
Artist:
<input name="artist" type="text" size="30
Order By:
<select name="sort_order">
<option value="songtitle" selected>Song Title</option>
<option value="artist">Artist</option></select>
<input type="submit" name="name2" value="Find my song"> </form>

PHP:

<?php

$songtitle_search=$songtitle;
$artist_search=$artist;

//+++++++++++++++++++++++++++++++++++++++++++++++++++++++BEGIN PHP++++++++++++++++++++++++++++++++++++++++++++++++++
// create connection
$connection = mysql_connect('localhost', pianoman_pianoma, jabber44031672);

// test connection
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}

// select database
$db = mysql_select_db("pianoman_pianotest", $connection);

// test selection
if (!$db) {
echo "Couldn't select database!";
exit;
}


$limit=20; // rows to return
$numresults=mysql_query("SELECT songtitle, artist
FROM karaoke
where songtitle LIKE '%$songtitle%'
AND artist LIKE '%$artist%'");
$numrows=mysql_num_rows($numresults);


// next determine if offset has been passed to script, if not use 0
if (empty($offset)) {
$offset=0;
}



// create SQL statement
$sql = "SELECT songtitle, artist
FROM karaoke
where songtitle LIKE '%$songtitle%'
AND artist LIKE '%$artist%'
ORDER BY $sort_order ASC
limit $offset,$limit";

//$sql_total = "select songtitle from karaoke";

// execute SQL query and get result
$sql_result = mysql_query($sql,$connection);
//$sql_count = mysql_num_rows($sql_result);

//$sql_total_query = mysql_query($sql_total,$connection);
//$sql_total_num = mysql_num_rows($sql_total_query);

// start results formatting

//*************Search Results at top of Page
if ($numrows<$limit) {
$thru=$numrows;
}
else {

$thru=$offset+$limit;

}

if ($thru>$numrows) {
$thru=$thru-($thru-$numrows);
}

$offset_plus=$offset+1;
echo "<b>Displaying Results $offset_plus thru $thru of $numrows Songs</b><p>";

// next we need to do the links to other results

if ($offset>=1) { // bypass PREV link if offset is 0
$prevoffset=$offset-$limit;
print "<a href=\"$PHP_SELF?offset=$prevoffset&songtitle=$songtitle_search&artist=$artist_search&sort_order=$sort_order\"><b><u>Previous Page</u></b></a> &nbsp; | &nbsp; \n";
}

// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

//for ($i=1;$i<=$pages;$i++) { // loop thru
// $newoffset=$limit*($i-1);
// print "<a href=\"$PHP_SELF?offset=$newoffset&songtitle=$songtitle_search&artist=$artist_search&sort_order=$sort_order\">$i</a> &nbsp; \n";
//}

// check to see if last page
if (!(($numrows)==$thru) && $pages!=1) {
// not last page so give NEXT link
$newoffset=$offset+$limit;
print "<a href=\"$PHP_SELF?offset=$newoffset&songtitle=$songtitle_search&artist=$artist_search&sort_order=$sort_order\"><b><u>Next Page</u></b></a>\n";
}
echo "</tr></td></table>";
//*************Close Results at top of page

echo "<p><TABLE width=\"95%\" BORDER=1 cellpadding=\"2\" cellspacing=\"0\" align=\"center\">";
echo "<TR bgcolor=\"#990000\"><TH><font color=\"#FFFFFF\">Title</font></TH><TH><font color=\"#FFFFFF\">Artist</font></TH>";

// format results by row
while ($row = mysql_fetch_array($sql_result)) {
$songtitle = $row["songtitle"];
$artist = $row["artist"];

echo
"<TR nowrap><TD>$songtitle</TD><TD>$artist</TD></TR>";



}

echo "</TABLE>";

if ($numrows<$limit) {
$thru=$numrows;
}
else {

$thru=$offset+$limit;

}

if ($thru>$numrows) {
$thru=$thru-($thru-$numrows);
}

$offset_plus=$offset+1;
echo "<center><p><b>Displaying Results $offset_plus thru $thru of $numrows Songs</b><p>";

// next we need to do the links to other results

if ($offset>=1) { // bypass PREV link if offset is 0
$prevoffset=$offset-$limit;
print "<a href=\"$PHP_SELF?offset=$prevoffset&songtitle=$songtitle_search&artist=$artist_search&sort_order=$sort_order\"><b><u>Previous Page</u></b></a> &nbsp; | &nbsp; \n";
}

// calculate number of pages needing links
//$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
//if ($numrows%$limit) {
// has remainder so add one page
// $pages++;
//}

//for ($i=1;$i<=$pages;$i++) { // loop thru
// $newoffset=$limit*($i-1);
// print "<a href=\"$PHP_SELF?offset=$newoffset&songtitle=$songtitle_search&artist=$artist_search&sort_order=$sort_order\">$i</a> &nbsp; \n";
//}

// check to see if last page
if (!(($numrows)==$thru) && $pages!=1) {
// not last page so give NEXT link
$newoffset=$offset+$limit;
print "<a href=\"$PHP_SELF?offset=$newoffset&songtitle=$songtitle_search&artist=$artist_search&sort_order=$sort_order\"><b><u>Next Page</u></b></a>\n";
}



// free resources and close connection
mysql_free_result($sql_result);
mysql_close($connection);


//+++++++++++++++++++++++++++++++++++++++++++++++++++++++END PHP++++++++++++++++++++++++++++++++++++++++++++++++++
?>

 

[sleipnir214]

When your code runs this line:

$sql = "SELECT songtitle, artist
FROM karaoke
where songtitle LIKE '%$songtitle%'
AND artist LIKE '%$artist%'
ORDER BY $sort_order ASC
limit $offset,$limit";


What is $sql being set to when you use inputs that return values?
What is $sql being set to when you use inputs that don't return values?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Nina2001]

It should only evaluate the songtitle or artist fields in the karaoke table if the input field (songtitle or artist)are NOT empty. This PHP works on the other song search PHP/SQL form with partial values, or when one of the fields are empty.

 

[sleipnir214]

I understand that.

However, what is the value of $sql when you get unexpected output?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Nina2001]

I think it is set to null. Here's what the settings look like in myAdmin.

http://www.pianomandj.com/sql.gif

 

[sleipnir214]

I'm not making myself clear.

If you print $sql to the browser, what is printed?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Nina2001]

Did I mention I'm new at this? This is the first PHP script I've tried to implement. I'm mimicing an existing php form. By printing $sql to the browser do you mean this?

I inserted a echo $sql and this is what came back in the browser:

SELECT songtitle, artist FROM karaoke where songtitle LIKE '%wild%' AND artist LIKE '%%' ORDER BY songtitle ASC limit 0,20

 

[sleipnir214]

I have found a couple of things.

First and least important, you're missing a "</tr>" tag in your HTML output. It's at the end of the table row that displays the column headings "Title" and "Artist".

Second and the source of your troubles, your data in your database is formatted in Unicode. The problem is that MySQL's LIKE operator doesn't speak unicode, so if you give MySQL a string of "wild", MySQL is trying to match that string against "{00}w{00}i{00}l{00}d" ("{00}" being the character with ASCII value zero), which will never match.

LIKE can match a single character in a unicode string.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[Nina2001]

THANK YOU! THANK YOU! THANK YOU! Something so dumb as importing in the wrong format. That was it. It's working great now. I REALLY appreciate all your help.