newbie tearing hair out select box/database link

Beginners - Learning PHP
#1

HI,

I am VERY new to PHP…written a little test page to allow users to select a date so they can view their disc golf scores from a the relevant round. I can get the pull-down menu working from the dataase AND pull the data from the databse IF I give it the right date BUT can’t get the value selected in the pull-down menu to go into the code to pull up the data…I am being stupid but please any advice would be SO well received.

I basically need to know what’s wrong with the code below and if I should open a new page to display the results from the database or can I keep the whole thing on one page.

First PHP Script <?php mysql_connect(localhost,myusername,mypassword) or die("Unable to connect to database"); @mysql_select_db("myusername_discgolf") or die("Unable to select database myusername_discgolf"); $query = "SELECT DISTINCT date FROM scores ORDER BY date"; $result = mysql_query($query) or die("Query failed"); while ($line=mysql_fetch_array($result, MYSQL_ASSOC)) { foreach ($line as $col_value) { print "".$col_value; } } ?> <?php $sqlquery = "SELECT * FROM scores WHERE date = $_POST['sel_date']"; $result = mysql_query($sqlquery); $number = mysql_numrows($result); $i = 0; if ($number < 1) { print "There Were No Results for Your Search"; } else { while ($number > $i) { $membername = mysql_result($result,$i,"Name"); $hole1 = mysql_result($result,$i,"Hole 1"); print "

Name: $membername
Hole 1: $hole1

"; $i++; } } ?>
#2

Hi mr gammon,

You dont need to use a foreach loop when using mysql_fetch_array. This will automatically return your data as an associative array. No need for other loops. Try this instead:

$query = "SELECT DISTINCT date FROM scores ORDER BY date"; $result = mysql_query($query) or die("Query failed");

while ($line=mysql_fetch_array($result))
{

echo “<OPTION VALUE=”".$line[‘date’]."">".$line[‘date’].“n”;

}

Hope that helps. :)

#3

Wow, that’s a quick response.

Thank you for your advice…I will play with this bit of code but it is the second half of the code I can’t get to work.

My pull-down menu works fine and pulls dates from my database…what i want then is for the user to press the submit button and then I want to display results from my database from the selected date. So it is the way of passing the selected date to the second php script segment that it driving me crazy…

any ideas? cheers again

#4

Ok, your problem is you have your drop down called sel_date and your input button is also called sel_date. This will create two variables with the same name, so a conflict arises. The second problem is in your WHERE clause. This must always be between apostrophes for maximum efficiency.

Firstly, rename your drop down menu to:

Then try this:

if ($_POST['sel_date'])

{

$options = $_POST[‘options’];

$sqlquery = “SELECT * FROM scores WHERE date = ‘options’”;

$result = mysql_query($sqlquery);

if (mysql_num_rows($result)==0)
{

print “There Were No Results for Your Search”;

}
else

{

while ($rows = mysql_fetch_array($result))
print "

Name: $rows['membername']
Hole 1:$rows['hole1']

";

}

}

That should work ok for you. :)

#5

THANK YOU…this seems to have done the trick! now onto the next problem :wink:[/quote]

#6

HI,

Sorry to bother you again but everything is working nicely now thanks to you…EXCEPT getting my $_POST[‘options’] variable to be used by my second script.

My first page test2.php works nicely creating a pull-down menu with dates from my database. This then calls test4.php when the submit button is pressed.

IF I use 2004-12-05 as the date passed directly to the SELECT query on my database my table is drawn all correct.
IF I print $_POST[‘options’] on test4.php then it displays 2004-12-05…
BUT IF I try to use this $_POST[‘options’] variable to pull the data from the database I get an empty table.

What is happening to my date?? why can’t I use this variable to actually query my database?

I also get the following error when I use $_POST[‘options’] directly into my SELECT query rather than creating a new variable $datetouse = $_POST[‘options’];

“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 ‘’ at line 1” ???

Can you tell I am at the end of my tether?? this was only supposed to be a fun experiment on a little fun website for my friends!???

I would appreciate any help, advice, verbal slapping you can manage!

test2.php

First PHP Script <?php

mysql_connect(localhost,x090705,sh00micat) or die(“Unable to connect to database”);

@mysql_select_db(“x090705_discgolf”) or die(“Unable to select database x090705_discgolf”);

$query = “SELECT DISTINCT date FROM scores ORDER BY date”;
$result = mysql_query($query) or die(“Query failed”);

while ($line=mysql_fetch_array($result))
{

echo “<OPTION VALUE=”".$line[‘date’]."">".$line[‘date’].“n”;

}
?>

test4.php

First PHP Script <?php // create connection to database $conn = mysql_connect(localhost,x090705,sh00micat) or die("Unable to connect to database"); // select database $db = mysql_select_db("x090705_discgolf", $conn) or die("Unable to select database x090705_discgolf"); // create SQL statement $sql = "SELECT * FROM scores WHERE date = $_POST['options']"; // execute SQL query and get result $sql_result = mysql_query($sql,$conn) or die(mysql_error()); // start results formatting echo ""; // format results by row while ($row = mysql_fetch_array($sql_result)) { $playername = $row["Name"]; $holeone = $row["Hole 1"]; $holetwo = $row["Hole 2"]; echo ""; } echo "
Name Hole 1 Hole 2
$playername $holeone $holetwo
"; ?>

HELP!!!

#7

and relax…I have finally sorted it with ‘$_POST[options]’; instead of $_POST['options];

thank you for just being there to bounce my frustration off !!

Sponsor our Newsletter | Privacy Policy | Terms of Service