Retrieve data from table

Question

HB25 0 Junior Poster in Training

15 Years Ago

Hi
I will explain what I am trying to do:
I have used MYPHP admin to create a database where user booking details will be stored in a table called bookings.
Now I need to retrieve all the bookings for a specific date. For achieving this I have created two pages one is html page for user to type the date and click on get bookings and the second page is a php page where it will have the database connection and the MySQL query.
But it only returns a blank page. Below are my html and php pages code.
Any help will be highly appreciated
Kind Regards
HB25
Html page

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Bookings Details </title>
</head>
<body>
<p>Enter the client and Booking date below to chek bookings information </p>
<form action="../actions/bookings_details.php" method="post">

<p><label for="date">Booking Date:</label><br />
<input id="%D  %M  %Y" name="%D  %M  %Y" type="text" /></p>

<p><input type="submit" value="check Booking" /></p>
</form>

</body>
</html>

PHP page

<html>
<head>
<title>Bookings  Details</title>
</head>
<body>
<?php
$con = mysql_connect("xxxxxx","xxxxxxx","xxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("xxxxxxxx", $con);

 SELECT * FROM bookings WHERE startdate=$_POST[%D  %M  %Y];
mysql_close($con);
?>

</body>
</html

mysql

3 Contributors
14 Replies
199 Views
1 Week Discussion Span
Latest Post 14 Years Ago Latest Post by Josh Connerty

All 14 Replies

Josh Connerty 20 Unverified User

15 Years Ago

Hi
I will explain what I am trying to do:
I have used MYPHP admin to create a database where user booking details will be stored in a table called bookings.
Now I need to retrieve all the bookings for a specific date. For achieving this I have created two pages one is html page for user to type the date and click on get bookings and the second page is a php page where it will have the database connection and the MySQL query.
But it only returns a blank page. Below are my html and php pages code.
Any help will be highly appreciated
Kind Regards
HB25
Html page
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Bookings Details </title>
</head>
<body>
<p>Enter the client and Booking date below to chek bookings information </p>
<form action="../actions/bookings_details.php" method="post">

<p><label for="date">Booking Date:</label><br />
<input id="%D  %M  %Y" name="%D  %M  %Y" type="text" /></p>

<p><input type="submit" value="check Booking" /></p>
</form>

</body>
</html>
PHP page
<html>
<head>
<title>Bookings  Details</title>
</head>
<body>
<?php
$con = mysql_connect("xxxxxx","xxxxxxx","xxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("xxxxxxxx", $con);

 SELECT * FROM bookings WHERE startdate=$_POST[%D  %M  %Y];
mysql_close($con);
?>

</body>
</html

You didn't seem to actually make the query in the PHP document.

Where you wrote

mysql_select_db("xxxxxxxx", $con);
SELECT * FROM bookings WHERE startdate=$_POST[%D %M %Y];
mysql_close($con);

you need to actually send the query. For example what you would do is

$date = $_POST[%D  %M  %Y];
$query = mysql_query("SELECT * FROM bookings WHERE startdate='$date'");

If you give me a few minutes and if you want me to I will re-write your code and correct some issues you are having. Also I don't know if this is right but I don't think you are allowed percentages signs in a name attribute. I may be wrong.

Josh Connerty 20 Unverified User

15 Years Ago

Ok so where the loop is (while( $row = mysql_ ) you need to change the array's to the names of each field on the rows of your table.

HTML FILE:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Bookings Details </title>
</head>
<body>
<p>Enter the client and Booking date below to chek bookings information </p>
<form action="../actions/bookings_details.php" method="post">

<p><label for="D M Y">Booking Date:</label><br />
<input id="D M Y" name="D M Y" type="text" /></p>

<p><input type="submit" value="Check Booking" /></p>
</form>

</body>
</html>

PHP FILE:

<html>
<head>
<title>Bookings  Details</title>
</head>
<body>
<?php
$con = mysql_connect("xxxxxx","xxxxxxx","xxxxxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("xxxxxxxx", $con);
$date = $_POST['D M Y'];
$q = mysql_query( "SELECT * FROM bookings WHERE startdate='$date'" );
while( $row = mysql_fetch_array( $q ) ) {
    echo $row['date']."<br />";
    echo $row['availbale']."<br />";   
}
mysql_close($con);
?>

</body>
</html>

Try this out and see what you get.

The main problem was you wasn't executing the query correctly and you weren't asking PHP to echo out the results set.

Josh Connerty 20 Unverified User

15 Years Ago

Ok can you use a client like phpMyAdmin to view the tables themselves?

If so do the dates indeed look like that?

What is the field name in wich the dates come under?

Have you changed the echo $row to echo $row ?

This should echo out the start dates.

Give these a try and so what you come up with.

Josh Connerty 20 Unverified User

15 Years Ago

It would seem that you will need to run a mysql_num_rows check on your query, it seems that the query is returning no matches or start dates.

echo mysql_num_rows( $q );
// $q being whatever variable you ran the mysql_query under.

Also I'm a bit un-nerved with your method. I would imagine using timestamps would make things much easier.

That way you convert the date put in to a timestamp then it would select any timestamps that are closer to the curent time because the timestamp would be a bigger number. If you catch my drift.

You should look into it, also there are functions that convert Y/M/D to a timestamp.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

HB25 0 Junior Poster in Training · Answer 1 · 2009-04-21T14:17:36+00:00

Hi Josh Connerty
Thank you for your help, it looks like the query working but PHP does not echo out the results.
For your information the date is stored in the database in this format ( Y M D).
I have tried the following dates and I did not get any error message but PHP does not echo out the results.
01-04-2009
01/04/2009
2009-04-01
209/04/01

Any more advice ?
Kind Regards
HB25

HB25 0 Junior Poster in Training · Answer 2 · 2009-04-22T04:09:51+00:00

Hi
Thank you for helping me out with this problem, please find below answers to your questions:

Ok can you use a client like phpMyAdmin to view the tables themselves?
Yes
If so do the dates indeed look like that?
2009-04-01
What is the field name in wich the dates come under?
startdate
Have you changed the echo $row to echo $row ?
yes
This should echo out the start dates.
theier is no data at all echo out

Any more advice?

Kind Regards
HB 25

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 3 · 2009-04-22T13:25:53+00:00

1. I would never use form element's names like, Y M D, ie., with spaces. Try,

<input id="dmy" name="dmy" type="text" />

And in the php script,

$date = mysql_real_escape_string($_POST['dmy']);

mysql_real_escape_string escapes all the escape characters and prevent sql injections.
Also, Instead of executing the query directly, you can put it in a variable and then pass it to mysql_query. Its easier to print a variable to know what is the query you are passing to mysql_query. Ie.,

$date = $_POST['dmy'];
$query = "SELECT * FROM bookings WHERE startdate='$date'";
echo $query;
$result = mysql_query($query);

Execute the query on backend (phpmyadmin) and see if it return any rows. If it doesn't, then,
1. You either don't have any records for that date (OR)
2. You have a different date format in the table (or while passing it in the query)

HB25 0 Junior Poster in Training · Answer 4 · 2009-04-25T19:52:02+00:00

Hi
Sorry for not replying in the past days I was out in town.
I have tried all the above but no successes yet, I am using the below code but I am getting this Resource id #2.
Any Idea what is Resource id #2?

$result = mysql_query("SELECT * FROM bookings WHERE startdate = '".$_POST['date']."'");

$result_array = mysql_fetch_array($result);
  
    echo($result);

Josh Connerty 20 Unverified User · Answer 5 · 2009-04-26T02:10:24+00:00

Try typing your query into phpMyAdmin and see if that returns any rows for the query.

It seems that the query will not return the rows due to the fact MySql will not use that format of time. It could also be that you don't have a start date that is exactly the time in wich you are unputting but I kinda thought that was obvious so you wouldn't make that mistake.

HB25 0 Junior Poster in Training · Answer 6 · 2009-04-26T15:18:09+00:00

Hi Josh
I have run query into phpMyAdmin and it did return the right number of rows, please see attached image.
When I click on create php code and copy and paste that query to my php page it does not return any rows.

$sql = 'SELECT * FROM `bookings` WHERE startdate=\'2009-04-01\' LIMIT 0, 30 ';

Any more advice ?
Kind Regards
HB25

HB25 0 Junior Poster in Training · Answer 7 · 2009-04-26T16:25:40+00:00

Hi Josh
Just to let you know that i have managed to solve my problem below is the code i have used and thank you very much for all your kind help.

$date = mysql_real_escape_string($_POST['y-m-d']);
$result = mysql_query("SELECT * FROM bookings WHERE startdate = '$date'");
echo "<table border='1'>
<tr>
<th>Client ID</th>
<th>Booking ID</th>
<th>Arrival</th>
<th>Departure</th>
<th>Adults</th>
<th>children</th>
<th>Room Type</th>
<th>Requirements</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['clientID'] . "</td>";
	echo "<td>" . $row['bookingID'] . "</td>";
	echo "<td>" .$row['startdate'] . "</td>";
	echo "<td>" .$row['enddate'] . "</td>";
	echo "<td>" .$row['adults'] . "</td>";
	echo "<td>" .$row['children'] . "</td>";
	echo "<td>" .$row['roomtype'] . "</td>";
	echo "<td>" .$row['requirements'] . "</td>";

  echo "</tr>";
  }
echo "</table>";

Josh Connerty 20 Unverified User · Answer 8 · 2009-04-26T20:09:02+00:00

Great so you solved the issue by mysql_real_escape ing the string?

Just for others that may come across this post.

Also would you not consider adding a timestamp as apose to a formatted date?

HB25 0 Junior Poster in Training · Answer 9 · 2009-04-27T02:49:40+00:00

Hi Josh
This is only a college work and for the time been this is fine. But do you know what will be the if statement to check user input date is exist in the datable or return a message to the user? And where we should place the if statement?

How is your database skill I may need some advice on Normalizations.

Kind Regards
HB 25

Josh Connerty 20 Unverified User · Answer 10 · 2009-04-28T08:36:49+00:00

You would just have to count the result of the rows for example when before you echo out the results (in the mysql_fetch_array) you would do this:

// count the number of entries retuned by the query
if( @mysql_num_rows( $result ) == 0 ) {
//if there isn't any give an error
die('There we no matched dates found in the database.');
} else {
//if there is echo out the results set
while( $row = mysql_fetch_array( $result ) ) {
//echo out your results...
}
}

Retrieve data from table

Recommended Answers Collapse Answers

All 14 Replies

Recommended Answers