Newbie Search help

Search form using PHP to connect to a MySQL database

[adredd]

Hi all,

Extreme newbie here, only me second day of mucking around with PHP and MySQL.

I'm trying to build a search form that will look through a MySQL database.

Using a bit of code that Jatar gave to someone else, I tried modifying it to my own needs, but keep coming up with the same results:

"select * from releases where id= and artist=
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/website/search.php on line 13"

Here's the PHP code I'm using:

<?
$host = "localhost";
$user = "username";
$pass = "password";
$dbname = "databasename";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$sql = "select * from tablename where id=" . $_POST['artist'] . " and artist=" . $_POST['artist'] . ""; echo $sql;
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) {
echo "<p>",$row['id'],": ",$row['artist'];
}

?>

Any ideas on what I'm doing wrong here?

Thanks in advance!
Todd

[Timotheos]

Hi adredd,

Welcome to WebmasterWorld!

Your script is not picking up the form variable that has been posted to it. Your form should have something like the following tag
<form name="myform" method="post" action="myscript.php">

Hopefully its that easy of a fix.

Tim

[adredd]

Hi Tim,

Here's what I've got in the form:

<form name="form1" method="post" action="search.php">
<input type="text" name="textfieldName" size="24"><input type="submit" name="submitButtonName">
</select>
</form>

Where search.php is the script that I posted earlier.

Could name="textfieldName" be the problem?

Thanks!

[Timotheos]

That's it Adredd. Change that to <input type="text" name="artist" size="24"> and you've got it. You're a quick leaner ;-) Now on to your next challenge getting the sql statement to work.

Tim

[adredd]

Thanks, Tim, but that still doens't seem to be getting it. I'm still getting the "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource" on line 13.

Line 13:
while ($row = mysql_fetch_array($query)) {

Which I'm guessing ir referring to:

Line 10:
$sql = "select * from releases where artist=" . $_POST['artist'] . ""; echo $sql;

So perhaps it's my syntax in the query? I'm not quite sure WHY I'm using " . $_POST['artist'] . " (again, I swiped this code from a previous post by jatar) so perhaps that's it?

Thanks for your help!
Todd

[coopster]

I see you are echoing out the $sql statement -- do you notice that the string comparison doesn't have any quotation marks around it? Those are required. Try this:

$sql = "select * from releases where artist='" . $_POST['artist'] . "'"; echo $sql;

[adredd]

Thanks, Coopster, no more errors. BUT, all I'm getting as a result is:
select * from releases where artist=''

I guess this is where the echo comes from, right?

OK, so now on to making it look pretty. Thanks!

[adredd]

All right, so now I can't get it to spit out any results.

Here's what I've got:

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);

$sql = "select * from releases where artist='" . $_POST['artist'] . "'"; echo $sql;
$query = mysql_query($sql);

echo "<table border=1>\n";
echo "<tr><td><b>ARTIST</b></td><td><b>RELEASE</b><td><b>DISC</b></tr>\n";

while ($row = mysql_fetch_array($query)) {
printf("<tr><td>%s</td><td>%s</td><td>%s</tr>\n",
$row[0], $row[1], $row[2], $row[3]);
}
echo "</table>\n";
?>

The resulting page simply says:
select * from releases where artist=''
ARTISTRELEASEDISC

(this last row is the table)

I'm guessing it has something to do with printf?

Thanks in advance!
Todd

[Timotheos]

It still doesn't look like you're getting you're POST variable. The sql statement should read
select * from releases where artist='Timotheos'
If you need my artist bio for your database then let me know ;-)

You can put this at the top to see all the post variables:
print_r($_POST);
If that does nothing then you still have problems getting the form variables into your script. Must be a typo somewhere or something.

Tim

[adredd]

Hmmm... That got it. Only thing I had to change in my form was POST. I originally had it as lowercase.

Umm... forgive a *really* stupid question here, but I'm guessing PHP is case sensitive?

"Damnit Tim, I'm a designer, not a programmer!"

Thanks for all your help!

[Timotheos]

Hmmm... That got it. Only thing I had to change in my form was POST. I originally had it as lowercase.

Umm... forgive a *really* stupid question here, but I'm guessing PHP is case sensitive?

Hmmm, that surprises me.

"Damnit Tim, I'm a designer, not a programmer!"

Hehe, join the dark side ;-)

Tim

[adredd]

OK, so it shouldn't be case sensitive? Hmmm.

Perhaps it WAS working before then. Using that bit of code you gave me:

print_r($_POST);

I was able to see the results unformatted. It then spit out the results of:

echo "<tr><td><b>ARTIST</b></td><td><b>RELEASE</b><td><b>DISC</b></tr>\n";

However, after that it's blank, so perhaps it was working all along and I just have everyhting below that wrong. Here's what I've got:

while ($row = mysql_fetch_array($query)) {
echo "<p>",$row['artist'],$row['release'],$row['disc'];
}
echo "</table>\n";

I'm guessing that it's the $row['etc'] parts that are wrong here...

Thanks, Tim.

[adredd]

OK, I think I'm on to something. Just playin around a bit with the code I see that

while ($row = mysql_fetch_array($query)) {
echo "<p>",$row['artist'],$row['release'],$row['disc'],$row['genre'],$row['format'];

seems to all run together, like

ArtistReleaseDisc

Instead of

Artist <table column> Release <table column> Disc

So what should the syntax there be?

[adredd]

AHA! Found it!

Guess I should have been reading up a bit more on my PHP coding. HTML tags need to placed within quotes and separated by commas.

Thanks for all your help, Tim!

Todd

[adredd]

AHA! But now I've added a twist...

Now I want the search form to have a pulldown, i.e. search for _____ in pulldown menu (artist/release/disc)

I've named the pulldown list 'criteria' and changed by query to:

$sql = "select * from releases where '" . $_POST['criteria'] . "'='" . $_POST['submission'] . "'";

my form looks like this:

<form name="form1" method="POST" action="searchNEW.php">
<input type="text" name="submission" size="24"><select name="criteria" size="1">
<option value="artist">Artist</option>
<option value="release">Release</option>
<option value="disc">Disc</option>
</select><input type="submit" name="submitButtonName">

and sadly, no results are passed back to me, save for the table header and "Array ( [submission] => Radiohead [criteria] => artist [submitButtonName] => )

(Radiohead is what I searched for...)