PHP MySQL "INSERT INTO" query error...

  • nomadpixi
  • Newbie
  • Newbie
  • nomadpixi
  • Posts: 5

Post 3+ Months Ago

Hi there,
I'm a real noob, so if this question sounds silly, I apologise in advance. I have form data that I am trying to insert, and I keep getting an error. I have the following. Is it the quotes? What am I doing wrong? My Db connection is fine, but the query is failing. Any help greatly appreciated.

Code: [ Select ]
<?php
//add form ids into variables-variabe = form id
 
    $firstName = $_POST['fName'];
    $lastName = $_POST['lName'];
    $addrOne = $_POST['addr1'];
    $addrTwo = $_POST['addr2'];
    $suburb = $_POST['suburb'];
    $postCode = $_POST['postCode'];
    $state = $_POST['state'];
    $phone = $_POST['contactNumber'];
    $email = $_POST['email'];
    $pw = $_POST['passwd'];
 
//link to dB
require_once 'courses.inc.php';
 
//query statement
 
$query = "INSERT INTO tblmembers('firstName','lastName','addressOne','addressTwo','suburb','state','contactPhone','email','password') VALUES('$firstName','$lastName','$addrOne','$addrTwo','$suburb','$postCode','$state','$phone','$email','$pw')";
 
 
//execute query
 
$result = mysql_query($query);
 
if (!$result)
    {
        exit('Error inserting data into database');
    }
    echo "$firstName your details have been successfully added";
 
?>
  1. <?php
  2. //add form ids into variables-variabe = form id
  3.  
  4.     $firstName = $_POST['fName'];
  5.     $lastName = $_POST['lName'];
  6.     $addrOne = $_POST['addr1'];
  7.     $addrTwo = $_POST['addr2'];
  8.     $suburb = $_POST['suburb'];
  9.     $postCode = $_POST['postCode'];
  10.     $state = $_POST['state'];
  11.     $phone = $_POST['contactNumber'];
  12.     $email = $_POST['email'];
  13.     $pw = $_POST['passwd'];
  14.  
  15. //link to dB
  16. require_once 'courses.inc.php';
  17.  
  18. //query statement
  19.  
  20. $query = "INSERT INTO tblmembers('firstName','lastName','addressOne','addressTwo','suburb','state','contactPhone','email','password') VALUES('$firstName','$lastName','$addrOne','$addrTwo','$suburb','$postCode','$state','$phone','$email','$pw')";
  21.  
  22.  
  23. //execute query
  24.  
  25. $result = mysql_query($query);
  26.  
  27. if (!$result)
  28.     {
  29.         exit('Error inserting data into database');
  30.     }
  31.     echo "$firstName your details have been successfully added";
  32.  
  33. ?>
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

You can get ahold of any error messages MySQL may be emiting by using the mysql_error() function.
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

try: removing single quotes on db column names and you left off zipcode column. So, you need to replace POSTCODE with whatever that column name is.

Code: [ Select ]

$query = "INSERT INTO tblmembers(firstName,lastName,addressOne,addressTwo,suburb,POSTCODE,state,contactPhone,email,password) VALUES('$firstName','$lastName','$addrOne','$addrTwo','$suburb','$postCode','$state','$phone','$email','$pw')";
  1. $query = "INSERT INTO tblmembers(firstName,lastName,addressOne,addressTwo,suburb,POSTCODE,state,contactPhone,email,password) VALUES('$firstName','$lastName','$addrOne','$addrTwo','$suburb','$postCode','$state','$phone','$email','$pw')";


:-) happy coding.
  • nomadpixi
  • Newbie
  • Newbie
  • nomadpixi
  • Posts: 5

Post 3+ Months Ago

thanks so much for your responses joebert and devilwood. I do think I may have bigger fish to fry. I took a look at the mySQL database and to my horror the autoincrement has seemed to been lost from my primary column memberId. I think I'm going to try to reconstruct that table and try it again.

@ joebert: the errorno code that is supposed to go in my php include sent to the database, is that supposed to go in the middle of the if statement? Will get back to you with the results. Thanks for bearing with the noob!

Code: [ Select ]
<?php
//this link connects to the server
    $link = mysql_connect('xxx','xxx','xxx');
        if(!$link)
            {
                exit('Unable to connect to the server');
                }
$conn = mysql_select_db('courses', $link);
        if(!$conn)
            {
                exit('Unable to link to database courses');
               
                }
        echo 'Successfully connected to the courses database';
 
?>
  1. <?php
  2. //this link connects to the server
  3.     $link = mysql_connect('xxx','xxx','xxx');
  4.         if(!$link)
  5.             {
  6.                 exit('Unable to connect to the server');
  7.                 }
  8. $conn = mysql_select_db('courses', $link);
  9.         if(!$conn)
  10.             {
  11.                 exit('Unable to link to database courses');
  12.                
  13.                 }
  14.         echo 'Successfully connected to the courses database';
  15.  
  16. ?>
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

If there's an error related to the query, the text from mysql_error() will be available after this line.

Code: [ Select ]
$result = mysql_query($query);
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Are you using phpmyadmin for your mysql? Adding the autoinc should be a simple dropdown or checkbox.

I imagine you have some type of text for php nearby as sometimes in my opinion it's easier and quicker for me to find answers in a book that I'm extremely familiar with. Though the net can get you quite far with php. Just remember to use php.net if there's a function that you're unsure how to use. Usually you can find the answer quicker on proper syntax, parameters, usage, EXAMPLES, etc. Instead of having to wait around for someone to respond especially on the usage of a function. Get a quick example and keep truckin' and maybe do some trial and error on a local machine. :-)

http://us2.php.net/manual/en/function.mysql-error.php
  • nomadpixi
  • Newbie
  • Newbie
  • nomadpixi
  • Posts: 5

Post 3+ Months Ago

Thanks so much to you both. I have fixed my increment problem, which allowed the query to work now. (yeah!)

@devilwood, the problem for the autoincrement was that the window for phpMyAdmin kept crashing before I was able to adjust the autoincrement column details. Seems ok now though.

@joebert, for future use, I'd like to understand the use of the mysql_error thingy. If I'm to understand this, it is a testing routine if your query comes back from mysql with unable to connect message but no definitive breakage of your website. Is that right? Again, sorry if this sounds dumb. I'm more of a visual person, swimming in a backend ocean :(
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

Here is how you could use mysql_error(); function.
Code: [ Select ]
$result = mysql_query($sql) or die(mysql_error());


I'm a visual learner as well :lol:
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Glad you got it working.

Bogey does have some nice graphic work. I'd say be on the lookout for him building some very nice total applications soon. I know he was writing some php classes so he's really under par for creating some complete projects in the near future developed entirely by himself. Anytime a graphic person gets the hang of programming you get a mean beast. I can do alot but when it comes to some frontend eye candy I have to get my sis or one of her friends and most graphic people I know, I'm sorry to say, usually out huggin' trees instead of meeting deadlines.
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

devilwood wrote:
Bogey does have some nice graphic work. I'd say be on the lookout for him building some very nice total applications soon. I know he was writing some php classes so he's really under par for creating some complete projects in the near future developed entirely by himself. Anytime a graphic person gets the hang of programming you get a mean beast.

lol umm.. thanks? Where does this fit in the topic? Also, why do you think I can do graphic work?
  • devilwood
  • Silver Member
  • Silver Member
  • User avatar
  • Posts: 436

Post 3+ Months Ago

Could have sworn I've seen some from your site, but I know I've seen some from the ozzu competitions. Just some motivation to namadpixi who sounds like a graphic person crossing over. :-)
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8397
  • Loc: USA

Post 3+ Months Ago

devilwood wrote:
Could have sworn I've seen some from your site, but I know I've seen some from the ozzu competitions. Just some motivation to namadpixi who sounds like a graphic person crossing over. :-)
Oh, I see.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13503
  • Loc: Florida

Post 3+ Months Ago

nomadpixi wrote:
@joebert, for future use, I'd like to understand the use of the mysql_error thingy. If I'm to understand this, it is a testing routine if your query comes back from mysql with unable to connect message but no definitive breakage of your website. Is that right? Again, sorry if this sounds dumb. I'm more of a visual person, swimming in a backend ocean :(


If you're getting a message about being unable to connect, then mysql_error() isn't going to be of much help. The mysql_error() function is mostly useful for times when you know you've got an established connection, but may have forgotten to select a database or need to track down an SQL syntax error.

It's been so long since I've had a connection error with MySQL that I'm really not sure how to track those down easily anymore. I would start with my login/server details and make sure there's no typos, then I would walk through the connection string for mysql_connect() and make sure there's no typos.
From there I would attempt to connect to the server using a known working application to make sure my details are good.
  • nomadpixi
  • Newbie
  • Newbie
  • nomadpixi
  • Posts: 5

Post 3+ Months Ago

Hi everyone.

Thank you all for your help. I did get my page working finally.

@joebert:thanks so much for the (mysql_error) code bit. This is what helped me fix the page(!) :)

I figure if anyone is as green as me, they might benefit from my angst, so -
Here's what worked for me...

Code: [ Select ]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>process form script page</title>
<link href="main.css" rel="stylesheet" type="text/css" />
</head>
<body>

<?php include 'menu.inc.php' ?>
//dB connect was not faulty

<?php require_once 'courses.inc.php' ?>
<?php

// username and password sent from form
$mymemberNumber=$_POST['memberNumber'];
$mypassword=$_POST['password'];


$query = "SELECT `memberId`,`password` FROM tblmembers WHERE `memberId` = '$mymemberNumber' AND `password` = '$mypassword'";
$result = mysql_query($query) or die(mysql_error());
$numrows = mysql_num_rows($result);


// Mysql_num_row is counting table row
$rowCount = mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($rowCount==1){
// Register and redirect to file "enrollSuccess.php"
session_register("mymembnum");
session_register("mypassword");
header("location:enrollSuccess.php");
}
else {
echo "Wrong Username or Password";
}
?>

</body>
</head>
  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  2. "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  3. <html xmlns="http://www.w3.org/1999/xhtml">
  4. <head>
  5. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  6. <title>process form script page</title>
  7. <link href="main.css" rel="stylesheet" type="text/css" />
  8. </head>
  9. <body>
  10. <?php include 'menu.inc.php' ?>
  11. //dB connect was not faulty
  12. <?php require_once 'courses.inc.php' ?>
  13. <?php
  14. // username and password sent from form
  15. $mymemberNumber=$_POST['memberNumber'];
  16. $mypassword=$_POST['password'];
  17. $query = "SELECT `memberId`,`password` FROM tblmembers WHERE `memberId` = '$mymemberNumber' AND `password` = '$mypassword'";
  18. $result = mysql_query($query) or die(mysql_error());
  19. $numrows = mysql_num_rows($result);
  20. // Mysql_num_row is counting table row
  21. $rowCount = mysql_num_rows($result);
  22. // If result matched $myusername and $mypassword, table row must be 1 row
  23. if($rowCount==1){
  24. // Register and redirect to file "enrollSuccess.php"
  25. session_register("mymembnum");
  26. session_register("mypassword");
  27. header("location:enrollSuccess.php");
  28. }
  29. else {
  30. echo "Wrong Username or Password";
  31. }
  32. ?>
  33. </body>
  34. </head>
  • tiffix
  • Student
  • Student
  • User avatar
  • Posts: 65
  • Loc: kenya

Post 3+ Months Ago

after executing the query use=>

Code: [ Select ]
echo mysql_error();


instead of

Quote:
27.if (!$result)
28. {
29. exit('Error inserting data into database');
30. }



this give you a high chance of knowing what the real error is...

Post Information

  • Total Posts in this topic: 15 posts
  • Users browsing this forum: No registered users and 153 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.