Insert Data Into A Database

[Paul]

Hi all,

Please take a look at the following code.

 

//Retreive the user id from the registration
$q = "SELECT user_id FROM dsf_users WHERE email='$e'";

$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

//Assign the user_id to an array to be picked up by the INSERT later on
while ($dsf_users = mysqli_fetch_array($r, MYSQLI_ASSOC))
	{
		//Does it pick up the user id correctly?
		echo 'The user id is' . $dsf_users['user_id'] .' ';
	}

if (mysqli_num_rows($r) == 1)
{
	//If it worked (it did) then insert the curtain quote into the DB, including the user_id
	//Check that there is a session containing all the quote information
	print_r($_SESSION);

	if ($_SESSION['quote_type'] == 'curtains')
	{
		$q = "INSERT INTO curtains 
		(user_id, drop, track_pole, track_pole_width, heading_tape, lining, fullness, interlined, fabric_width, current_status, curtain_total_cost, curtain_quote_date)
		 VALUES 
		 ({$dsf_users['user_id']}, {$_SESSION['drop']}, {$_SESSION['track_pole']}, {$_SESSION['track_pole_width']}, {$_SESSION['heading_tape']}, {$_SESSION['lining']}, 
		 {$_SESSION['fullness']}, {$_SESSION['interlined']}, {$_SESSION['fabric_width']}, 'quote', {$_SESSION['curtain_total_cost']}, NOW() )";

		$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
	}
	else
	{
		echo 'it worked for blinds';
	}
}

 

Hopefully the comments should explain what I'm trying to do but in a nutshell: A user requests a quote with a form. The quote information is saved to a session. The user is then required to register in order to save their quote. After completing the registration form the above code picks up the user id and then togethor with the quote information in the session inserts the lot into a curtains table in a DB.

 

It didn't work. Here's the error log

 

[b]The user id is31[/b] Array ( [quote_type] => curtains [drop] => 10 [track_pole_width] => 20 [fabric_width] => 139 [track_pole] => track [lining] => regular_lining [heading_tape] => three_inch [fullness] => 2 [interlined] => no [curtain_total_cost] => 27.5 )

An error has occurred in script 'C:\xampp\htdocs\Di's Soft Furnishings\register.php'
on line 151: [i]Query: INSERT INTO curtains (user_id, drop, track_pole, track_pole_width, heading_tape, lining, fullness, interlined, fabric_width, current_status, curtain_total_cost, curtain_quote_date) VALUES (, 10, track, 20, three_inch, regular_lining, 2, no, 139, 'quote', 27.5, NOW() )[/i]
MySQL Error: 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 'drop, track_pole, track_pole_width, heading_tape, lining, fullness, interlined, ' at line 2
Date/Time: 7-7-2011 13:13:37
Array ( [GLOBALS] => Array *RECURSION* [_POST] => Array ( [first_name] => fffffffff [last_name] => jjjjjjjjjjjj [email] => ff@ff.co.uk [password1] => test1 [password2] => test1 [submit] => Register to save your quote [submitted] => TRUE ) [_GET] => Array ( ) [_COOKIE] => Array ( [phpSESSID] => 8182bkr3mu2f81qh3i6fi88q60 ) [_FILES] => Array ( ) [page_title] => Registration page [_SESSION] => Array ( [quote_type] => curtains [drop] => 10 [track_pole_width] => 20 [fabric_width] => 139 [track_pole] => track [lining] => regular_lining [heading_tape] => three_inch [fullness] => 2 [interlined] => no [curtain_total_cost] => 27.5 ) [dbc] => mysqli Object ( [affected_rows] => -1 [client_info] => mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $ [client_version] => 50007 [connect_errno] => 0 [connect_error] => [errno] => 1064 [error] => 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 'drop, track_pole, track_pole_width, heading_tape, lining, fullness, interlined, ' at line 2 [field_count] => 1 [host_info] => localhost via TCP/IP [info] => [insert_id] => 0 [server_info] => 5.5.8 [server_version] => 50508 [sqlstate] => 42000 [protocol_version] => 10 [thread_id] => 112 [warning_count] => 0 ) [trimmed] => Array ( [first_name] => fffffffff [last_name] => jjjjjjjjjjjj [email] => ff@ff.co.uk [password1] => test1 [password2] => test1 [submit] => Register to save your quote [submitted] => TRUE ) [p] => test1 [e] => ff@ff.co.uk [ln] => jjjjjjjjjjjj [fn] => fffffffff [q] => INSERT INTO curtains (user_id, drop, track_pole, track_pole_width, heading_tape, lining, fullness, interlined, fabric_width, current_status, curtain_total_cost, curtain_quote_date) VALUES (, 10, track, 20, three_inch, regular_lining, 2, no, 139, 'quote', 27.5, NOW() ) [r] => [a] => 383d20e38bb88e2f126fd40736e2fe6d [dsf_users] => ) 

 

As you can see the script picked up the user_id OK (it's 31) and the session contains data. It says that there's something wrong with my syntax. Now, looking at the query in the error message (in italics) it has every value except the user_id. Yet thescript has pciked up the user_id (in bold).

 

It's going to be something obvious, but I can't see it!

 

Any help would be greatly appreciated. Sorry it's a bit of an epic.

 

Thanks

Paul

[Paul Swanson]

I think you may have more than a single issue on this. The error message is indicating the problem is happening before your values, it's actually with the field names. I suspect the culprit is drop, which is a command in SQL. Try wrapping that field name in back-ticks (it's the character that shares the tilde (~) key).

 

Your SELECT query should only return one row, since you shouldn't be allowing duplicate user_id's, so there is no need for a while loop. And I would check for the number of rows before attempting to fetch it.

 

$q = "SELECT user_id FROM dsf_users WHERE email='$e'";
$r = mysqli_query ($dbc, $q) OR trigger_error ("Query: $q\n<br />MySQL Error: " . mysql_error ($dbc));

if (mysqli_num_rows ($r) == 1) {
 $dsf_user = mysqli_fetch_array ($r, MYSQLI_ASSOC); // note the variable name is singular now, only going to be one user

 if ($_SESSION['quote_type'] == 'curtains') {
$q = "INSERT INTO curtains 
 	(user_id, `drop`, track_pole, track_pole_width, heading_tape, lining, fullness, interlined, fabric_width, current_status, curtain_total_cost, curtain_quote_date) 
 	VALUES ({$dsf_user['user_id']}, {$_SESSION['drop'}, '{$_SESSION['track_pole']}', {$_SESSION['track_pole_width']},'{$_SESSION['heading_tape']}', '{$_SESSION['lining']}', {$_SESSION['fullness']}, '{$_SESSION['interlined']}', {$_SESSION['fabric_width'}, 'quote', {$_SESSION['curtain_total_cost']}, NOW())";

$r = mysqli_query ($dbc, $q) OR trigger_error ("Query: $q\n<br />MySQL Error: " . mysql_error ($dbc));
 } else {
echo 'it worked for blinds';
 }
}

I've placed single quotes around some of the values, since in the error message those values appear to be strings. I think the while loop was messing you up with the user_id, as it would move to the next pointer in the array fetched by MySQL, but that row wouldn't have any values as the query would only return one row.

[Antonio Conte]

May I make some suggestions about your structure? I know very little about curtains, but I see you use a lot of "hard coded values".

 

Have you thought about making additional tables to keep data integrity in place? regular_lining, three_inch, etc is possible to misspell (and not so easy to work with)

 

Lets say you add a table lining:

lining_id (auto_increment, primary key)

lining_type (Varchar)

 

It would allow you to use lining_id as the value to save in your database. It would also make number functions easyer. Like to show how many curtains with regular lining you have. This can be extended to most of your other things to.

 

SELECT lining_type, count(lining_id)
FROM lining
WHERE lining_id = 8
GROUP BY lining_type asc, lining_id asc

 

Example output:

Regular lining (15 available)

Silk lining (3 available)

Ragged lining (7 available)

 

Just a suggestion.

[Paul]

Antonio and Paul,

 

Thankyou both very much for your solutions and suggestions. Paul, it worked in the end, your answer was spot-on. Antonio, I think I see what you are saying.

 

I believe that one of the problems that Paul was answering below (the one where he needed to insert single quotes on various values) was caused by this sort of situation. The way I understand Paul's answer is that the quotes were needed because my form was created in such a way that MYSQL was treating the values as text. I notice that the only fields that didn't need the quotes were fields whoes values were integers. Would that be correct thinking? Would Antonios suggestion have reduced or eliminated this type of problem?

 

Is there a danger, though, of creating so many tables that the wood cannot be seen for the trees? I suspect that there's a fine line here.

 

I must confess I know very little about curtains and roman blinds either, it's my wifes business, but I've picked a humdinger (do you know that word?) as my first php project.

 

Thanks again.

Paul

[Antonio Conte]

Hey Paul

 

It would solve you problems, yes, but using quotes is probably easier. I understand what you say about threes and the forest, and I agree with you. Database systems need to work, that's the most important. If you get lost by the structure, it's probably a little too tricky for you.

 

Integers don't need quotes in queries. Text does. The name DROP is likely the problem, but might work with quotes. try curtain_drop our something instead if it does not work.