I wonder whether someone can help me please.
I've put together the script below that displays a list of dates from a mySQL database.
<?php
mysql_connect("host", "user", "password")or
die(mysql_error());
mysql_select_db("database");
?>
<form>
<select>
<?php
$result = "SELECT userdetails.userid, finds.dateoftrip, detectinglocations.locationname, finds.findid, finds.userid, finds.locationid, detectinglocations.locationid, finds.findname, finds.finddescription FROM userdetails, finds, detectinglocations WHERE finds.userid=userdetails.userid AND finds.locationid=detectinglocations.locationid AND finds.userid = 1 GROUP By dateoftrip ORDER BY dateoftrip DESC";
$result =mysql_query($result);
while ($data=mysql_fetch_assoc($result)){
?>
<option value ="<?php echo $data['findid'] ?>" ><?php echo $data['dateoftrip'] ?></option>
<?php } ?>
</select>
</form>
What I'd now like to do is upon a date being selected, retrieve the associated values from the fields 'findname' and 'finddescription, inserting them in a table on my page.
I've spent hours trying to get this to work, without any success. I just wondered whether someone could perhaps give me a helping hand please and let me know what I need to do to retrieve the results.
Many thanks
Updated Code
<?php
mysql_connect("host", "user", "password")or
die(mysql_error());
mysql_select_db("database");
?>
<form>
<select>
<?php
$result = "SELECT dateoftrip, findid, userid, locationid, findname, finddescription FROM finds GROUP By dateoftrip ORDER BY dateoftrip DESC";
$result =mysql_query($result);
while ($data=mysql_fetch_assoc($result)){
?>
<option value ="<?php echo $data['findid'] ?>" ><?php echo $data['dateoftrip'] ?></option>
<?php } ?>
</select>
</form>
let me know what I need to do to retrieve the results
what you need is:
1.repair query
SELECT
userdetails.userid AS userdetails_userid,
finds.dateoftrip,
detectinglocations.locationname,
finds.userid AS finds_userid,
finds.locationid AS finds_locationid ,
detectinglocations.locationid AS detectinglocations_locationid ,
finds.findname,
finds.finddescription
FROM
userdetails,
finds,
detectinglocations
WHERE
finds.userid=userdetails.userid AND
finds.locationid=detectinglocations.locationid AND
finds.userid = 1
GROUP By
finds.dateoftrip
ORDER BY
finds.dateoftrip DESC
2.insert generating code into variable and then echo
while ($data=mysql_fetch_assoc($result)) {
$options .="<option value =\"". $data['userdetails_userid'] ."\">". $data['finds_locationid'] ."</option>";
}
echo "<select>". $options ."</select>";
Your form needs a destination php file to submit to, and a submit button:
<form method="post" action="handle_submission.php">
...
<input type="submit" value="Search" name="search"/>
</form>
handle_sumbission.php
will receive the form selection as entries in $_POST. I'd start with a separate script like this and once you get it working, you could fold them into one script if you really want to.
Here is an AJAX script that should do the job.
//JQuery library must be included
//Script for page
<script type="text/javascript">
$(document).ready(function() {
$("#findname").blur(function()
{
//remove all the class add the messagebox classes and start fading
$("#msgbox").removeClass().addClass('messagebox').text('Checking...').fadeIn("slow");
//check the username exists or not from ajax
$.post("user_availability.php",{ username:$(this).val() } ,function(data)
{
if(data=='no') //if username not avaiable
{
$("#msgbox").fadeTo(200,0.1,function() //start fading the messagebox
{
//add message and change the class of the box and start fading
$(this).html('Username not available to register').addClass('messageboxerror').fadeTo(900,1);
});
}
else
{
$("#msgbox").fadeTo(200,0.1,function() //start fading the messagebox
{
//add message and change the class of the box and start fading
$(this).html('Username available to register').addClass('messageboxok').fadeTo(900,1);
});
}
});
});
});
</script>
Put your PHP mySQL query in a script names user_availability.php and have the script actioned once the user leaves the field. The script is looking for a 'yes' or 'no' if mySQL finds a match but you could also have the PHP script return anything you want.
Here is an example of how the PHP script works:
// Connect to MYSQL database //
$host = 'localhost';
$user = 'root';
$pass = 'root';
$db = 'table';
$connect = mysql_connect($host,$user,$pass) or die ("Couldn't connect to mySQL!");
mysql_set_charset('utf8',$connect);
mysql_select_db($db) or die ("Couldn't find the database");
// Form value sent by AJAX
$user_name=$_POST['username'];
$length = strlen($user_name);
if ($length < 5) {
echo "no"; exit();
}
// Grab all users in the database
$query = "SELECT username FROM users";
$result = mysql_query($query);
$count = mysql_num_rows($result); $i=0;
while ($i < $count) {
$existing_user = mysql_result($result, $i, 'username');
if ($user_name == $existing_user)
{
exit();
}
$i++;
}
//No matches so return OK to proceed with name
echo "yes";
As allready mentioned the drop down menu will be userside. If you want this to work with php only, you could send a GET value back to the page. Catch that value and do the query with it ...
The link in the dropdown list could be:
<a href="mypage.php?date=2012-03-08">datetodisplay</a>
You would catch it like:
$date = $_GET['date'];
Then You'd have to change you're query and add the $date into a WHERE clause like:
$query = "SELECT * FROM WHERE date = " . $date;
Don't know if I give what You need ... and of course You should check this GET value and real_escape it before using it in a query ...