Hi there,

I'm having trouble getting the following to work...

I have a page that pulls data from the table "bookings" and displays them on a page under the headings: confirmed bookings, unconfirmed and cancelled bookings.

When a booking is made it is unconfirmed until the admin says otherwise by clicking a button. The database sets the "status" field as 0 by default to indicate this. The admin logs in and will see all unconfirmed, confirmed and cancelled bookings on screen.

Basically the next step is to make an unconfirmed booking to confirmed (status value of 1) or cancelled (status value of 2) through the click of a button or link. I know i'll need an update query to change the "status" field from 0 to 1 if the booking is being confirmed or from 0 to 2 for cancelled bookings.

I'm completely new to php and mysql and dont really know what code I need and the syntax to put it in so please be nice 🙂

I have this code so far for generating the results from tables for the admin...


<?php

$connect = mysql_connect("localhost", "root", "") or die(); 
mysql_select_db("cottages") or die();

$query="SELECT * FROM bookings WHERE status=0 ORDER BY datesubmitted DESC";
$result=mysql_query($query);

$num=mysql_num_rows($result);

echo "<b><font size=2>Unconfirmed Bookings:</b></font><br></br>";
echo "<b><font size=1><u>Date Submitted</u> <u>Cottage Name</u> <u>Start Date</u> <u>End Date</u> <u>First Name</u> <u>Surname</u></b></font><br></br>";

$i=0;
while ($i < $num) 
{
	$datesubmitted=mysql_result($result,$i,"datesubmitted");
	$cottagename=mysql_result($result,$i,"cottagename");
	$startdate=mysql_result($result,$i,"startdate");
	$enddate=mysql_result($result,$i,"enddate");
	$firstname=mysql_result($result,$i,"firstname");
	$surname=mysql_result($result,$i,"surname");

	echo "<font size=1> $datesubmitted $cottagename $startdate $enddate $firstname $surname</font><br></br> ";
	echo "<form><input type='submit' method='POST' name='submit' action='confirmbooking.php' value='Confirm Booking'> </form><br></br> ";
	//echo "<font size=1><input type='submit' name='cancel' action='cancelbookings.php' value='Cancel Booking'> <br></br>";
	//echo "<font size=1><a href='mailto:". $row['email'] ."?subject=CCN:%20Bookings&body=Dear ". $row['firstname']." " .$row['surname']." '>Email</a> <br></br>";
	$i++;
}

I also attempted updating with this code but nothing happens...


<?php

$submit = $_POST['submit'];

if (isset($_POST['submit']))
{		
	$connect = mysql_connect("localhost", "root", "") or die(); 
	mysql_select_db("cottages") or die();

$query="UPDATE bookings 
		SET status='1' 
		WHERE status='0'
		";

$result=mysql_query($query);

$num=mysql_num_rows($result);

}

else
	echo "failed";
?>

If somebody could give me the code or point out where i'm going wrong I would greatly appreciate it! Thank you 🙂

Jamie

    First off, this query

    UPDATE bookings SET status='1' WHERE status='0'

    changes ALL rows that have status '0' to status '1'. Also, unless the status field is varchar or text, that should be

    UPDATE bookings SET status=1 WHERE status=0

    I've no idea what the standard says, and it's possible that all existing DBMSs handle automatic typecasting, but looking at the code it suggest that you could also

    set status='sure, if I still feel like it next week'

    which I bleieve might fail silently on mysql (depending on config).

    Always check your queries for errors

    $result = mysql_query($query);
if (!$result)
{
	error_log(mysql_errno($link).': ' . mysql_error($link).PHP_EOL.$query);
	# depending on what query doesn't go through, you might want to exit, throw an exception
	# or just continue code execution further down
}
else
{
	# query ok
}

    [/code]

      Write a Reply...