Page 1 of 1

Passing variable from form to sql query

Posted: Wed Feb 24, 2010 5:27 am
by dave.hawksworth
I want to use the variable input into a form to be the WHERE value in an sql query.
I have tried the following code which seems to work ie it generates no error messages but the query returns an empty result. If I run the query by inputing the value entered in the form directly in the query it works.
Can anyone suggest what is the problem or is the method itself incorrect.
Thanks
Dave
FORM "form1.html"
<form method="post" action="array_script.php">
0.<br/>
<input type="varchar" Day="Day"/><br/><br/>

<input type="submit" name="submit"/> </form>

PHP Script "array_script.php"

<?php

$Day = $_POST['Day'];



$username="user";
$password="abcdefg";
$database="use_db";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


$query="SELECT * FROM courttimes Where Day='$Day'";
$result=mysql_query($query);

$num=mysql_numrows($result);

echo "<b><center>Database Output</center></b><br><br>";

echo "<table border='1'>
<tr>
<th>Day</th>
<th>Date</th>
<th>Time1</th>
<th>Player1</th>
<th>Player2</th>
<th>Time2</th>
<th>Player3</th>
<th>Player4</th>

</tr>";

while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['Day'] . "</td>";
echo "<td>" . $row['Date'] . "</td>";
echo "<td>" . $row['Time1'] . "</td>";
echo "<td>" . $row['Player1'] . "</td>";
echo "<td>" . $row['Player2'] . "</td>";
echo "<td>" . $row['Time2'] . "</td>";
echo "<td>" . $row['Player3'] . "</td>";
echo "<td>" . $row['Player4'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysql_close($con);

?>

Re: Passing variable from form to sql query

Posted: Wed Feb 24, 2010 7:53 am
by N1gel
You could try a few conditional statments to check the query has executed and if not print out the Query to make sure it looks like you think it should. Also try printing the mysql_error

Code: Select all

 
$query="SELECT * FROM courttimes Where Day='$Day'";
if($result=mysql_query($query))
{
    if($num=mysql_numrows($result) > 0)
    {
 
    }
    else
    {
        echo "No Rows Returned";
    }
}
else
{
     echo "Query Error";
     echo $query;
     echo mysql_error();
}
 

Re: Passing variable from form to sql query

Posted: Wed Feb 24, 2010 8:40 am
by dave.hawksworth
Thanks for the response.
I tried the code you suggested, and again no errors were returned. However I had added one more record to the database while trying out an update query which had no entry in the field Day and this record was returned by the query even though the other 20 records with Day = to the submitted variable were not displayed.

The result was as follows:-
Database Output


Day Date Time1 Player1 Player2 Time2 Player3 Player4
0000-00-00 0000-00-00 00:00:00 AA Vegas 0000-00-00 00:00:00 cc EE
So it appears the query is running but the value in $Day is a blank.
Any further thoughts.
Thanks
Dave

Re: Passing variable from form to sql query

Posted: Wed Feb 24, 2010 8:58 am
by N1gel
is this your problem

Code: Select all

 
<input type="varchar" Day="Day"/><br/><br/>
 
I think it should be

Code: Select all

 
<input type="varchar" name="Day"/><br/><br/>
 
A test would be to print out your Query before you execute it to see what it looks like.

Re: Passing variable from form to sql query

Posted: Wed Feb 24, 2010 9:29 am
by dave.hawksworth
That did'nt change the output.
I added a print statement for $ Day and it printed a blank, so the form is not passing the variable accross to the php script.
print "Day: {$_POST['Day']}<br />";



The result was:
Day:
Database Output


Day Date Time1 Player1 Player2 Time2 Player3 Player4
0000-00-00 0000-00-00 00:00:00 AA Vegas 0000-00-00 00:00:00 cc EE
The error may be in the form.
Any further thoughts.
Thanks
Dave

Re: Passing variable from form to sql query

Posted: Wed Feb 24, 2010 9:41 am
by N1gel
Try this to see if anything is being Posted.

Code: Select all

print_r($_POST);
If your not getting anything posted you need to look at your HTML form.

Re: Passing variable from form to sql query

Posted: Wed Feb 24, 2010 9:57 am
by dave.hawksworth
Sorry
You were right I made a slip up in the FTP transfer when I updated the form, so I didnt see the change.
The correct form is:-
<form method="post" action="array_script.php">
<br/>
<input type="varchar" name="Day"/><br/><br/>

<input type="submit" name="submit"/> </form>

This now gives this output:-

Day: Mon
Database Output


Day Date Time1 Player1 Player2 Time2 Player3 Player4
Mon 01-Jan-10 08:00:00 DH TP 08:15:00 BG EJ
Mon 01-Jan-10 08:45:00 Free Free 09:00:00 Free Free
Mon 01-Jan-10 09:30:00 Free Free 09:45:00 Free Free
Mon 01-Jan-10 10:15:00 Free Free 10:30:00 Free Free
Mon 01-Jan-10 11:00:00 Free Free 11:15:00 Free Free
Mon 01-Jan-10 11:45:00 Free Free 12:00:00 Free Free
Mon 01-Jan-10 12:30:00 Free Free 12:45:00 Free Free
Mon 01-Jan-10 13:15:00 Free Free 13:30:00 Free Free
Mon 01-Jan-10 14:00:00 Free Free 14:15:00 Free Free
Mon 01-Jan-10 14:45:00 Free Free 15:00:00 Free Free
Mon 01-Jan-10 15:30:00 Free Free 15:45:00 Free Free
Mon 01-Jan-10 16:15:00 Free Free 16:30:00 Free Free
Mon 01-Jan-10 17:00:00 Free Free 17:15:00 Free Free
Mon 01-Jan-10 17:45:00 Free Free 18:00:00 Free Free
Mon 01-Jan-10 18:30:00 Free Free 18:45:00 Free Free
Mon 01-Jan-10 19:15:00 Free Free 19:30:00 Free Free
Mon 01-Jan-10 20:00:00 Free Free 20:15:00 Free Free
Mon 01-Jan-10 20:45:00 Free Free 21:00:00 Free Free
Mon 01-Jan-10 21:30:00 Free Free 21:45:00 Free Free
Mon 01-Jan-10 22:15:00 Free Free 22:30:00 Free Free
Mon 01-Jan-10 23:00:00 Free Free 23:15:00 Free Free

Thanks a lot for your help, I can now move forward with the rest of the project.
Dave