4 Replies - 550 Views - Last Post: 11 March 2015 - 06:07 PM Rate Topic: -----

#1 Alexmackzie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 23-October 14

my code is not inserting the values into mysql Syntax Error

Posted 11 March 2015 - 03:43 PM

Hey! I am creating a booking system for meeting/group rooms for a school project.
This is the code that creates the tables, it worked well. make_tables.php
<?php
include_once("connect.php");

$tbl_available = "CREATE TABLE IF NOT EXISTS available (
									id int(11) NOT NULL AUTO_INCREMENT,
									roomnum varchar(50) NOT NULL,
					avail int(11) NOT NULL,
					prosjektor varchar(3) NOT NULL,
					etasje int(11) NOT NULL,
					PRIMARY KEY(id)
					)";
$query = mysqli_query($connect, $tbl_available);
if ($query === TRUE) {
		echo "<h3>available table created</h3>";
	} else {
		echo "<h3>available table NOT created</h3>";
	}
$tbl_confirms = "CREATE TABLE IF NOT EXISTS confirms (
								id int(11) NOT NULL AUTO_INCREMENT,
								roomnum varchar(50) NOT NULL,
								start_date datetime NOT NULL,
								end_date datetime NOT NULL,
								navn varchar(50) NOT NULL,
								PRIMARY KEY (id)
								)";
$query = mysqli_query($connect, $tbl_confirms);
if ($query === TRUE) {
		echo "<h3>confirms table created</h3>";
	} else {
		echo "<h3>confirms table NOT created</h3>";
	}

$tbl_insert = "INSERT INTO available (roomnum, avail, prosjektor, etasje)
								VALUES ('Rom 1', 1, 0, 1),
								('Rom 2', 1, 1, 1),
								('Rom 3', 1, 0, 1),
								('Rom 4', 1, 1, 1),
								('Rom 5', 1, 0, 1),
								('Rom 6', 1, 1, 1),
								('Rom 7', 1, 0, 1),
								('Rom 8', 1, 1, 1),
								('Rom 9', 1, 0, 1),
								('Rom 10', 1, 1, 1),
								('Rom 11', 1, 0, 2),
								('Rom 12', 1, 1, 2),
								('Rom 13', 1, 0, 2),
								('Rom 14', 1, 1, 2),
								('Rom 15', 1, 0, 2),
								('Rom 16', 1, 1, 2),
								('Rom 17', 1, 0, 2),
								('Rom 18', 1, 1, 2),
								('Rom 19', 1, 0, 2),
								('Rom 20', 1, 1, 2)";
$query = mysqli_query($connect, $tbl_insert);
if ($query === TRUE) {
		echo "<h3>Starting data inserted</h3>";
	} else {
		echo "<h3>NOT inserted</h3>";
	}
?>



Here is the Page where you reserve(not finished yet, but i would like to make the booking work, and then add on extra functions later on)reservations.php
<?php
// Database connection script
include ("connect.php");


// Clean out expired reservations
$clean = "SELECT c.*, a.avail
		  FROM confirms AS c
		  LEFT JOIN available AS a ON a.roomnum = c.roomnum
		  WHERE c.end_date < NOW()";
$freequery = mysqli_query($connect, $clean) or die (mysqli_error($connect));
$num_check = mysqli_num_rows($freequery);
if ($num_check != 0){
	while ($row = mysqli_fetch_array($freequery, MYSQLI_ASSOC)){
		$id = $row['roomnumber'];
		// Add back the room
		$updateAvailable = 1;
			
		// Delete the reserves
		$sql3 = "DELETE FROM confirms WHERE roomnumber='$dI' LIMIT 1";
		$query3 = mysqli_query($connect, $sql3);
		// Update the database with newly available rooms
		$sql3 = "UPDATE available SET avail='$updateAvailable' WHERE roomnum='$dI' LIMIT 1";
		$query3 = mysqli_query($connect, $sql3);
	}
}

// Get initial state of available rooms
$chart = "";
$sql = "SELECT * FROM available";
$query = mysqli_query($connect, $sql) or die (mysqli_error($connect));
// Loop and get all the data
while ($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
	// Assign table data to variables
	$id = $row['id'];
	$roomnum = $row['roomnum'];
	$avail = $row['avail'];
	$prosjektor = $row['prosjektor'];
	// Build display output
	// Display for available rooms
	if ($avail == 0){
		$chart .= '<div class="full"><div class="numSeats">The room is taken.</div></div>';		
	} else {
		// Display for available rooms - clickable inner div
		$chart .= '<div class="available"><div id="tbl_'.$id.'" class="numSeats" onclick="showRoom(this.id)">The room is free!</div></div>';		
	}
}
$chart .= '<div class="clear">';
	
?>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="reservations.css">
<script src="reservations.js"></script>
</head>
<body>
	<form name="name"" action="reservator.php" method="POST">
	<form action="<?php $_PHP_SELF ?>" method="POST">
		<fieldset>
			<legend>Booking Details: </legend>
			<label for="date">From when do you want to book?: </label><input id="date" type="datetime" name="date" pattern="[YYYY-MM-DD HH:MM:SS]">
			<br>
			<label for="date_end">when are you finished with the room?: </label><input id="date_end" type="datetime" name="date_end" pattern="[YYYY-MM-DD HH:MM:SS]">
			<br>
			<label for="velgRom">What room do you want to book?: </label><input id="velgRom" type="int" name="velgRom" pattern="[0-9]{1,2}">
			<br>
			<label for="numberOfPeople">How any people ar in your group?: </label><input id="numberOfPeople" type="number" name="numberOfPeople" min="2" max="5">
			<br>
			<label for="Prosjektor">Do you require a Beamer? </label>"<input id="Prosjektor" type="checkbox" name="prosjektor" value="Prosjektor">
			<br>
			<label for="name">Who are you?: </label>"<input id="name" type="text" name="name">
			<br>
			<input id="confirmBtn" type="submit" name="submit" href="workplz.php" >
		</fieldset>
<div id="wrapper">
   <div id="stage"></div>
   <div id="seats">
      <?php echo $chart; ?>   
   </div>
</div>
</body>
</html>


the code inserts the room they selected, datetime they want to book it from and datetime they want to book it to, into the database. This is where something goes wrong: reservator.php
<?php

include ("connect.php");

$tbl_insert = "INSERT INTO confirms (roomnum,start_date,end_date,)
	    VALUES ('velgRom', 'date', ''date_end)";
	    $sql = "UPDATE available SET avail=0 where id = 'velgRom'";
	$query = mysqli_query($connect, $tbl_insert);
	if ($query === TRUE) {
	        echo "<h3>Data inserted</h3>";
	    } else {
	        echo "<h3>NOT inserted</h3>";
	    }


?>


So as I run this, I am able to put in all the information. But I get NOT inserted, which means that something went wrong when the code tried to insert the values into the database. When running i get this error:"Parse error: syntax error, unexpected '"', expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in C:\wamp\www\php2\reservator.php on line 6" So the fault is at line 6, but i can't see it.

Thank you!
-Alex

This post has been edited by Alexmackzie: 11 March 2015 - 03:46 PM


Is This A Good Question/Topic? 0
  • +

Replies To: my code is not inserting the values into mysql Syntax Error

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4191
  • View blog
  • Posts: 13,240
  • Joined: 08-June 10

Re: my code is not inserting the values into mysql Syntax Error

Posted 11 March 2015 - 03:48 PM

the code you posted has no PHP syntax errors (though some SQL syntax errors).

but the error indicates an incorrect quote sequence.
Was This Post Helpful? 1
  • +
  • -

#3 Alexmackzie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 23-October 14

Re: my code is not inserting the values into mysql Syntax Error

Posted 11 March 2015 - 04:38 PM

im guessing that was the failing ''date_end instead of 'date_end'

<?php

include ("connect.php");

$tbl_insert = "INSERT INTO confirms (roomnum,start_date,end_date,)
	    VALUES ('velgRom', 'date', 'date_end')";
	    $sql = "UPDATE available SET avail=0 where id = 'velgRom'";
	$query = mysqli_query($connect, $tbl_insert);
	if ($query === TRUE) {
	        echo "<h3>Data inserted</h3>";
	    } else {
	        echo "<h3>NOT inserted</h3>";
			var_dump('date', 'date_end', 'velgRom');
	    }


?>


This time it ran correctly, but it returned NOT inserted. So something is still wrong.
So i used var_dump to find the values.
string 'date' (length=4)

string 'date_end' (length=8)

string 'velgRom' (length=7)

Did these not get saved from the form? Or am i using them wrongly?

Thank you!

This post has been edited by Dormilich: 12 March 2015 - 12:12 AM

Was This Post Helpful? 0
  • +
  • -

#4 ArtificialSoldier  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2016
  • View blog
  • Posts: 6,141
  • Joined: 15-January 14

Re: my code is not inserting the values into mysql Syntax Error

Posted 11 March 2015 - 05:03 PM

Your SQL query has an extra comma after end_date. Other than that, you are telling it to insert the text "velgRom", "date", and "date_end", not variables or form values. In the HTML, you have 2 form tags with different actions, the quotes in one of the form tags is messed up, and you have no closing form tag.
Was This Post Helpful? 1
  • +
  • -

#5 Alexmackzie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 23-October 14

Re: my code is not inserting the values into mysql Syntax Error

Posted 11 March 2015 - 06:07 PM

Yes, I managed to fix it:) Thank you again for all your help. I've learnt a lot today!

This post has been edited by Dormilich: 12 March 2015 - 12:11 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1