PHP/SQL Question - +10 points to the first right answer!

G

Gorbo

n00b
Joined
Apr 5, 2003
Messages
21
Code: 
	if ($_GET["id"] == 8)
	{ 
		if (isset($_POST[firstname]))
			{
	$db = mysql_connect("mysql1.000webhost.com", "a8426899_gorbo", "********");
	mysql_select_db("a8426899_pandora",$db)or die('Error, Cannot select or database');
	$sql = "INSERT INTO signups (firstname, lastname, city, state, country, email, sensitivity, fibromyalgia, myalgic, postviral, postinfectious, yuppie, chronicpain, migraine, arthritis, allergies, rhinitis, asthma, food, celiac, bowel, depression, anxiety, thyroid, comment) VALUES ('$firstname', '$lastname', '$city', '$state', '$country', '$email', '$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$potinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', '$comment')";
	$result = mysql_query($sql) or die('Error, insert query failed');
	echo "Thank you! Information entered.\n";
	
			}
			else
			{...


Output = Error, insert query failed


I cant figure out why this is... I sent the data via a form located in the ELSE statement, can anyone help me with this, all form Ids are the same as the variable names.
 
I forgot, the SQL database contains identical labels as the variable names.
 
First, you need some consistencies in your code.

First, connect to the db outside of the if statement first.
Second, try printing the sql command and seeing if the data gives it a syntax error.
Third, always try to use single quotes when dealing with verbal arrays - like ['field'].
Fourth, backquote columns in your sql statement like `column`.
Fifth, if you echo the SQL statement, use it in a program like PHPMyAdmin to get a more detailed error.
 
$sql = "INSERT INTO signups (..., postinfectious, ...) VALUES (..., '$potinfectious',...)";

potinfectious?
 
first, as suggested, add an echo $sql to see your query. also, echo out the error
... or die("... insert failed: " . mysql_error());

and i hope you've sanitized any form input and not blindly put it in your sql query
http://en.wikipedia.org/wiki/Sql_injection
as well as otherwise validating any submitted data. isset($_POST['firstname']) will be true if they left it blank, for example (if you've left that stuff out for brevity then so be it).
 
why is your mySQL query statement so long...if you are entering in the same order as the row forget adding the first part...

so from this
Code: 
	$sql = "INSERT INTO signups (firstname, lastname, city, state, country, email, sensitivity, fibromyalgia, myalgic, postviral, postinfectious, yuppie, chronicpain, migraine, arthritis, allergies, rhinitis, asthma, food, celiac, bowel, depression, anxiety, thyroid, comment) VALUES ('$firstname', '$lastname', '$city', '$state', '$country', '$email', '$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$potinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', '$comment')";
.

to this
Code: 
	$sql = "INSERT INTO signups VALUES ('$firstname', '$lastname', '$city', '$state', '$country', '$email', '$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$potinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', '$comment')";

now they both work the same...

just to make sure
1) when i have variables in strings i do { } around them. for example: '{$variable}' . this helps makes clarify it better
2) make sure the variables you're inputting in the mySQL database match with the variables set in the mySQL database for example a string into a varchar() and an integer into an int()
 
2) make sure the variables you're inputting in the mySQL database match with the variables set in the mySQL database for example a string into a varchar() and an integer into an int()
Click to expand...

I think we might be onto something here.

I have my checkbox's SQL fields set to varchar, what is their value when checked and not checked?
 
Valkhorn said:
First, you need some consistencies in your code.

First, connect to the db outside of the if statement first.
Click to expand...

now why is this important?
If you don't need to connect to the database, why do it every time? why not use the database only when you need to?
 
TempleDMDKrazd said:
why is your mySQL query statement so long...if you are entering in the same order as the row forget adding the first part...

so from this
Code: 
	$sql = "INSERT INTO signups (firstname, lastname, city, state, country, email, sensitivity, fibromyalgia, myalgic, postviral, postinfectious, yuppie, chronicpain, migraine, arthritis, allergies, rhinitis, asthma, food, celiac, bowel, depression, anxiety, thyroid, comment) VALUES ('$firstname', '$lastname', '$city', '$state', '$country', '$email', '$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$potinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', '$comment')";
.

to this
Code: 
	$sql = "INSERT INTO signups VALUES ('$firstname', '$lastname', '$city', '$state', '$country', '$email', '$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$potinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', '$comment')";
Click to expand...

you can also use heredoc syntax to break it into easier to read chunks
Code: 
$sql = <<<SQL //this  doesn't have to be called SQL, it can be any name you want
INSERT INTO signups 
(
firstname, lastname, city, state, country, email, 
sensitivity, fibromyalgia, myalgic, postviral, postinfectious, yuppie, chronicpain, migraine, arthritis, allergies, rhinitis, asthma, food, celiac, bowel, depression, anxiety, thyroid,
comment
)

VALUES 

(
'$firstname', '$lastname', '$city', '$state', '$country', '$email', 
'$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$potinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', 
'$comment'
)
SQL;//there must not be any leading or trailing spaces on this line!!!!
 
now why is this important?
If you don't need to connect to the database, why do it every time? why not use the database only when you need to?
Click to expand...

It was late or early when I wrote that. What I meant to say is that calling your database parameters should be in an include/extra file or you should use a database access layer or class.

It's called better design of code. Technically your database information should be an include so that the database layer is separate from the rest of the script because database access information can change.

SQL Error Output = Duplicate entry '0' for key 1
Click to expand...

You have a problem in your database structure. Which columns are the index/primary or are unique?
 
Okay, so I have managed to fix the auto incremental index.

The site is now recognizing that I want to fill it with data, it creates a new row, auto increments the whole bit... however now it just refuses to place the necessary data into the database.

I have fixed some of the errors, and the code now looks like:

Code: 
	$db = mysql_connect("mysql1.000webhost.com", "a8426899_gorbo", "****");
	mysql_select_db("a8426899_pandora",$db)or die('Error, Cannot select or database');
	$sql = "INSERT INTO signups (firstname, lastname, city, state, country, email, sensitivity, fibromyalgia, myalgic, postviral, postinfectious, yuppie, chronicpain, migraine, arthritis, allergies, rhinitis, asthma, food, celiac, bowel, depression, anxiety, thyroid, comment) VALUES ('$firstname', '$lastname', '$city', '$state', '$country', '$email', '$sensitivity', '$fibromyalgia', '$myalgic', '$postviral', '$postinfectious', '$yuppie', '$chronicpain', '$migraine', '$arthritis', '$allergies', '$rhinitis', '$asthma', '$food', '$celiac', '$bowel', '$depression', '$anxiety', '$thyroid', '$comment')";

	$result = mysql_query($sql);
	print mysql_error();
	echo "Thank you! Information entered.\n";

HTML Output = Thank you! Information entered
SQL Output = 4 (BLANK) (BLANK) (BLANK) (BLANK) (BLANK) (BLANK) ...

4 Being the current index.

Thank you guys so much for the help, I really do appreciate it.
 
Gorbo said:
HTML Output = Thank you! Information entered
SQL Output = 4 (BLANK) (BLANK) (BLANK) (BLANK) (BLANK) (BLANK) ...

4 Being the current index.

Thank you guys so much for the help, I really do appreciate it.
Click to expand...

How are you getting the values for each field? E.g. - how do you get the value for $firstname?
 
the form I am sending them with is directed to the same page, different IF statement, so the variables should stay filled upon POST?
 
Gorbo said:
the form I am sending them with is directed to the same page, different IF statement, so the variables should stay filled upon POST?
Click to expand...

can you show some code from your form fields and $_POST variables?
 
Great News!

I got the form to submit properly through firefox to the SQL backend.

The problem now is IE wont submit the exact same form.

http://tier10.com/index.php?id=8

Now I'm *really* stuck!

Code: 
 <input type="submit" name="submit" id="submit" value="Submit" />
 
Gorbo said:
Great News!

I got the form to submit properly through firefox to the SQL backend.

The problem now is IE wont submit the exact same form.

http://tier10.com/index.php?id=8

Now I'm *really* stuck!

Code: 
 <input type="submit" name="submit" id="submit" value="Submit" />
Click to expand...

I'm wondering if this might be what's causing IE to barf..
action="index.php?id=8"
 
nope, doesn't seem to have anything to do with the URL variables... button still wont act.
 
fyi, and you might already know this but i can just hit submit w/ no data in the form and it claims to have added data to the db
 
i dont know what type of HTTP request he is sending either POST or GET but make sure the data is set by

if(isset($_POST['firstname'])
$firstname = $_POST['firstname']

also when you define a table make sure the primary key is unique...in this case i would probably put the a field like

key int(5) NOT NULL auto_increment,
....
primary key(key));

and when you insert a row you can just pass NULL to that field and it will automatically increment it..and you can easily call it back with mysql_insert_id()
 
No offense, but this post is a shining example of what I hate about php. Theres nothing like mixing UI code with DB connections AND in-line SQL statements in <10 lines of code. The approach you're using doesn't scale well.

Split the code into functions/classes/etc. That will a) force you to organize your thoughts and b) make it easier for 1) us 2) YOU to understand.

Sorry if this is coming off sounding rude, but the first step to fixing the issue you're having here is to write clean and properly abstracted code. It will help you pinpoint the exact piece of code that fails.

In this case, you should abstract all the DB/SQL code into a separate class/file/function with functions that connect/execute queries/disconnect. Next step up from that would be a function, that given a set of parameters, builds a sql query and hands it off to DB class to process. And on top of that should be the UI/$_GET/$_POST stuff which pulls what it needs and calls that function.

I guarantee you that your problem will find itself if you structured your code in such a way.
 
wuggle said:
No offense, but this post is a shining example of what I hate about php. Theres nothing like mixing UI code with DB connections AND in-line SQL statements in <10 lines of code. The approach you're using doesn't scale well.

Split the code into functions/classes/etc. That will a) force you to organize your thoughts and b) make it easier for 1) us 2) YOU to understand.

Sorry if this is coming off sounding rude, but the first step to fixing the issue you're having here is to write clean and properly abstracted code. It will help you pinpoint the exact piece of code that fails.

In this case, you should abstract all the DB/SQL code into a separate class/file/function with functions that connect/execute queries/disconnect. Next step up from that would be a function, that given a set of parameters, builds a sql query and hands it off to DB class to process. And on top of that should be the UI/$_GET/$_POST stuff which pulls what it needs and calls that function.

I guarantee you that your problem will find itself if you structured your code in such a way.
Click to expand...

sorry to burst your bubble but any language is like that no matter if it connects to a DB or not. thats why OOP was invented...so it just doesn't pertain to PHP...
 
No offense, but this post is a shining example of what I hate about php. Theres nothing like mixing UI code with DB connections AND in-line SQL statements in <10 lines of code. The approach you're using doesn't scale well.
Click to expand...

I agree with Temple, this is possible in any language, including C++. Hell, for the first year of C++ I took they didn't even cover classes and OOP like they should have. In fact if I could teach programming I'd teach OOP and Class philosophy first, then get to the nuts and bolts.

With PHP you can easily use DB classes to keep things separate. In fact, at my current job we use a PEAR extension called Propel. Before that we used a nice DB class.

Split the code into functions/classes/etc. That will a) force you to organize your thoughts and b) make it easier for 1) us 2) YOU to understand.

Sorry if this is coming off sounding rude, but the first step to fixing the issue you're having here is to write clean and properly abstracted code. It will help you pinpoint the exact piece of code that fails.

In this case, you should abstract all the DB/SQL code into a separate class/file/function with functions that connect/execute queries/disconnect. Next step up from that would be a function, that given a set of parameters, builds a sql query and hands it off to DB class to process. And on top of that should be the UI/$_GET/$_POST stuff which pulls what it needs and calls that function.

I guarantee you that your problem will find itself if you structured your code in such a way.
Reply With Quote
Click to expand...

This isn't always the case :) Sometimes the problems can be quite elusive no matter how you structure it.
 
TempleDMDKrazd said:
sorry to burst your bubble but any language is like that no matter if it connects to a DB or not. thats why OOP was invented...so it just doesn't pertain to PHP...
Click to expand...

Yes, that is absolutely true. But for some reason, every single php project I happen to stumble upon is just painful to read. Take perl vs python for example. You can write beautiful perl and really bad python... but which one (on average) do you think is more prone to cluttering and bad coding? which one (on average) is easier to understand?

Valkhorn said:
This isn't always the case Sometimes the problems can be quite elusive no matter how you structure it.
Click to expand...

Yes, but it's 10x easier to debug once you clean up / abstract / separate out the code. I agree, there are some very very very difficult problems to debug, but that doesn't mean you get to write messy code and use that as an excuse :D
 
You must log in or register to reply here.
Back
Top