Values not inserted in database

@IllufoxApr 12.2005

I’ve set up a web application where I can add, remove and edit data in my client database. Everything worked fine until I recently added more form fields.

The edit and delete functions still work fine. When I add a new record via the insert form I get a success message but the record doesn’t show in the database.

However, I have no problem entering a new record in the database directly.

Both the form code and form processing code seem fine. I’ve double and triple checked every single line. I can only think that something is missing in the database, something that makes inserting records via the web working. Something like more indexes or such… (??)

I’m using ~~[I]~~phpMyAdmin[/I] and Dreamweaver.

Here is the code for those who wonder:

[code=php]<? // this code will display error messages if any… ini_set (‘display_errors’, 1); /// —> end displaying errors…

include(“dbinfo.inc.php”);

$company=$_POST[‘company’]; $address=$_POST[‘address’]; $city=$_POST[‘city’]; $state=$_POST[‘state’]; $zip=$_POST[‘zip’]; $url=$_POST[‘url’]; $contact=$_POST[‘contact’]; $phone=$_POST[‘phone’]; $mobile=$_POST[‘mobile’]; $fax=$_POST[‘fax’]; $email=$_POST[’email’]; $proposal_date=$_POST[‘proposal_date’]; $invoice_date=$_POST[‘invoice_date’]; $invoice_number=$_POST[‘invoice_number’]; $amount=$_POST[‘amount’]; $notes=$_POST[‘notes’]; $comments=$_POST[‘comments’]; $project=$_POST[‘project’]; $estimate=$_POST[‘estimate’]; $deposit=$_POST[‘deposit’]; $projecturl=$_POST[‘projecturl’];

mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( ‘Unable to connect to the database because: ‘ . mysql_error());

$query = “INSERT INTO content VALUES (”,’$company’,’$address’,’$city’,’$state’,’$zip’,’$url’,’$contact’,’$phone’,’$mobile’,’$fax’,’$email’,’$proposal_date’,’$invoice_date’,’$invoice_number’,’$amount’,’$notes’,’$comments’,’$project’,’$estimate’,’$deposit’,’$projecturl’)”; mysql_query($query);

echo “<center>Congrats! The record has been added successfully!</center>”; mysql_close(); ?> [/code]

to post a comment

PHP

43 Comments(s) _↴

@abudabitApr 12.2005 — #Two things:

Echo your query to see if something is missing, usually this is it. Compare it to your fields.

Second make sure your contents you are trying to insert don't contain any ' characters. This will also monkey things up.

@abudabitApr 12.2005 — #Also, I notice a lot of:

$es timate

You are using a soft wrap around in your text editor and not a hard one, right?

And numbers shouldn't be in '$variable' I believe, I always just do $variable if it is a numerical field.

@NogDogApr 12.2005 — #Check the result of the mysql_query to see if it will tell you more about what's wrong:

[code=php]
 $result = mysql_query($query);
 if($result !== FALSE)
 {
 if(mysql_affected_rows($result) == 0)
 {
 echo "<p>Database Error " . mysql_errno($result) . ": " . mysql_error() . "</p>n";
 }
 }
 else
 {
 echo "<p>ERROR: Unknown error occured while attempting to insert data into database.</p>n";
 }
 [/code]

@IllufoxauthorApr 12.2005 — #I echoed my query and didn't get any results.....so why do the values of the form not get carried over to the processing page? It worked before I added the following:

$notes=$_POST['notes'];

$project=$_POST['project'];

$projecturl=$_POST['projecturl'];

@IllufoxauthorApr 12.2005 — #The spaces happen when I paste the code into this forum. They don't exist in my original code, also, there's no wrapping....

@abudabitApr 12.2005 — #You entered the line: Echo $query, " "; and it didn't show anything? I doubt that. Enter that line after you set the $query variable and you will see a lot of useful information.

@IllufoxauthorApr 12.2005 — #I guess my code was wrong: echo "Query: " . mysql_query($query);

I added the code you provided above and got the following:

INSERT INTO content VALUES ('','test','test','test','cc','33333','swwww','wwww','44444','444','','','','','','','','','','','','')

That's exactly what I entered into the form.....so why doesn't it get added to the database??? I'm completely lost....

@NogDogApr 12.2005 — #Have you tried my suggestion yet to see if you can get PHP and MySQL to tell you more about what happened?

@IllufoxauthorApr 12.2005 — #Yes, just did and that's what I get:

ERROR: Unknown error occured while attempting to insert data into database.

@abudabitApr 12.2005 — #Hmm, count the number of fields you are attempting to insert vs. the number of columns in your table. They have to be the same. Also, I don't know if this matters but I make the primary index field NULL, not ''. Also are all of your columns character columns? If any of them are number columns you should really get rid of the quotes for those fields.

Otherwise I can't see anything wrong, so unless it is something not visible here like forgetting to actually add the columns instead of just adding them to my table create script (I have done that a lot), then it has to be one of those things listed in my first paragraph.

Remember that just one missing, additional, or misformated field in an insert command and the whole thing doesn't work.

@NogDogApr 12.2005 — #OK, let's try this (not sure if there is a mysql_error if the mysql_query() fails, but we'll find out ? ):

[code=php]
 $result = mysql_query($query);
 if($result == FALSE or mysql_affected_rows($result) == 0)
 {
 echo "<p>Database Error " . mysql_errno() . ": " . mysql_error() . "</p>n";
 }
 [/code]

@IllufoxauthorApr 12.2005 — #Yes, number of form fields and number of columns are the same (21).

Here is my database table":

[B]Field Type Attributes Null Default Extra [/B]

id int(3) No auto_increment

company varchar(50) No

address varchar(50) No

city varchar(30) No

state char(2) No

zip varchar(5) No

url varchar(100) No

contact varchar(50) No

phone varchar(14) No

mobile varchar(14) No

fax varchar(14) No

email varchar(100) No

proposal_date date No 0000-00-00

invoice_date date No 0000-00-00 invoice_number varchar(10) No

amount varchar(10) No

comments varchar(100) No

username varchar(30) No

password varchar(32) No

projecturl varchar(100) No

project varchar(100) No

estimate varchar(4) No

deposit char(3) No

notes text No

@IllufoxauthorApr 12.2005 — #Ok, now I get this:

Database Error 1136: Column count doesn't match value count at row 1

@abudabitApr 12.2005 — #21? I am counting 24.

You've got 22 fields in your insert, and 24 in your table. There's your problem.

@IllufoxauthorApr 12.2005 — #Yes, the database has three more: id, username, password

@IllufoxauthorApr 12.2005 — #Yes, I have user name and password in the database but I don't want this in the form....I guess I have to.....

@IllufoxauthorApr 12.2005 — #I would like to have a separate table for user name and password but I don't know how to connect it to the main table..... ?

@abudabitApr 12.2005 — #Remember that in an insert you need ALL of your columns represented.

Now to connect two tables together via a user, I give a user a userid number. It is a unique number, and any side table references the userprofile table via it (and visa versa). In the main userprofile table there are columns such as password, username, etc. next to that users userid number.

@IllufoxauthorApr 12.2005 — #The insert records works now again. Thanks so much for helping me solve this problem. Why didn't I think of that?

I know that I have the user id will connect the two tables but I don't know how to query correctly. This is what I have now:

$query="SELECT * FROM content WHERE username='$username'";

@abudabitApr 12.2005 — #That'll work, that will give you all the rows with the persons username. Depending on your settings it might be a case sensitive search. But while that is effective, it is an inferiour way of doing things. Check out how I link these two tables:

$userprofiles_tablename = 'userprofiles';

$userprofiles_table_def = "indexnumber INT(31) DEFAULT '0' NOT NULL AUTO_INCREMENT,";

$userprofiles_table_def .= "userid INT(30) DEFAULT '0' NOT NULL,";

$userprofiles_table_def .= "lastname CHAR(40) NOT NULL,";

$userprofiles_table_def .= "firstname CHAR(40) NOT NULL,";

$userprofiles_table_def .= "password CHAR(20) NOT NULL,";

$userprofiles_table_def .= "lastlogin BIGINT(40) DEFAULT '0' NOT NULL,";

$userprofiles_table_def .= "division CHAR(15) NOT NULL,";

$userprofiles_table_def .= "usertype CHAR(10) NOT NULL,";

$userprofiles_table_def .= "title CHAR(40) NOT NULL,";

$userprofiles_table_def .= "accesslevel INT(30) DEFAULT '0' NOT NULL,";

$userprofiles_table_def .= "contactnumber CHAR(30) NOT NULL,";

$userprofiles_table_def .= "email CHAR(200) NOT NULL,";

$userprofiles_table_def .= "flags CHAR(20) NOT NULL,";

$userprofiles_table_def .= "ftpuploadabletypes TEXT,";

$userprofiles_table_def .= "ftpuploadabledirectories TEXT,";

$userprofiles_table_def .= "notes TEXT,";

$userprofiles_table_def .= "PRIMARY KEY (indexnumber),";

$userprofiles_table_def .= "INDEX (userid, lastname, firstname, lastlogin, usertype, accesslevel, flags)";

$filefolders_tablename = 'filefolders';

$filefolders_table_def = "indexnumber INT(31) DEFAULT '0' NOT NULL AUTO_INCREMENT,";

$filefolders_table_def .= "foldernumber INT(30) DEFAULT '0' NOT NULL,";

$filefolders_table_def .= "parentnumber INT(30) DEFAULT '0' NOT NULL,";

$filefolders_table_def .= "location CHAR(100) NOT NULL,";

$filefolders_table_def .= "foldername CHAR(20) NOT NULL,";

$filefolders_table_def .= "adminid INT(30) DEFAULT '0' NOT NULL,";

$filefolders_table_def .= "minlevel INT(30) DEFAULT '0' NOT NULL,";

$filefolders_table_def .= "notes TEXT,";

$filefolders_table_def .= "PRIMARY KEY (indexnumber),";

$filefolders_table_def .= "INDEX (foldernumber, parentnumber, location, foldername, adminid)";

[/QUOTE]

You will note that the login information is in the userprofile table, and content is in side tables. Basically they log in, thier info is stored in sessions, and any changes they make to the content tables have a userid# reference column. That way their account can be attached to multiple content rows. Also numerical searches are faster than text ones. Also I just have to change thier userprofile row to update thier profile info instead of having to change every row of content they are in, for example if I wanted to change thier password. There are other ways that this is a superiour system, but it would take forever to list them.

A small site should have at least a seperate table for userprofile info, and a larger one should have all sorts of user tables such as userstats, usercore stuff, userprofile, usersettings, etc. All of these are linked together with the efficient and unique userid number.

Oh yeah, forgot to mention in the second table adminid is the usernumber of the user who owns that filefolder.

@IllufoxauthorApr 12.2005 — #Well, currently I have user name and password in the "content" table so the query statement works fine, however if I have username and password in a separate table called "login" I have to change the query so it compares the id from "login" with the id from "content". This is where I'm confused...

@abudabitApr 12.2005 — #Yes, I have user name and password in the database but I don't want this in the form....I guess I have to.....[/QUOTE]

Use sessions.

Have them log in via a userprofile table, put the data into a session variable, and when they create a content row include the related session variable in the insert. But like I mentioned above, best to link them with only a userid number. Much more efficient and in my mind secure.

@NogDogApr 12.2005 — #Note that you don't necessarily have to provide values for every column:

<i>
 </i>INSERT INTO table_name (col1, col2, col3) VALUES ('val1', 'val2', 'val3);
 ... or ...
 INSERT INTO table_name SET col1='val1', col2='val2', col3='val3';

@IllufoxauthorApr 12.2005 — #I read that storing login info in sessions is not very secure. Also, my clients don't create anything, they just view their personal info...

@IllufoxauthorApr 12.2005 — #My solution is to have the user name and password field in the table but not display this information in the report for security purposes.....This seems to work....

@abudabitApr 12.2005 — #Well, currently I have user name and password in the "content" table so the query statement works fine, however if I have username and password in a separate table called "login" I have to change the query so it compares the id from "login" with the id from "content". This is where I'm confused...[/QUOTE]

First off take the username and password out of the content table.

Then add a useridnumber column to both the content table and the userprofile table.

Then create a login system. When a enters a password / username it looks those up in the userprofile table. If the password matches the username, it puts all the data grabbed from the userprofile row into a session variable.

Make your content viewing / submission only accessible to people who have valid sessions. A simple if ($userprofile->useridnumber > 0) would work fine, although there are additional security precautions that can be done if you are working with money transactions on the site.

When a user submits data, have $userprofile->useridnumber in the insert field for the useridnumber.

Very simple once you actually go through it.

@abudabitApr 12.2005 — #I read that storing login info in sessions is not very secure. Also, my clients don't create anything, they just view their personal info...[/QUOTE]

Nothing is very secure by itself. When you login someone can sniff your data. That is why you layer security measures on top of each other.

Using sessions is still more secure than NOT using sessions. Sessions are just one additional layer to your security system. Other things that help are getting info from $_SERVER data and putting them into your session during login, and then checking to see that various $_SERVER subvariables are the same during each page load. Believe me, unless you are handling money or working for the DOD nobody is going to bother hacking past that stuff.

And to reiterate; when people say sessions are not secure they mean sessions are not secure in and of themselves as a security system. They ARE more secure than using nothing. Find me a high security site that doesn't use sessions as PART of thier security protocol.

@IllufoxauthorApr 12.2005 — #I already have a login and it uses sessions.

I also have a separate table with just the login info and the user id, but because I wasn't able to figure out how to define the query I ended up adding the login info to the main table.

So all I need to know is how to set up the query to compare two tables.

@abudabitApr 12.2005 — #I already have a login and it uses sessions.

I also have a separate table with just the login info and the user id, but because I wasn't able to figure out how to define the query I ended up adding the login info to the main table.

So all I need to know is how to set up the query to compare two tables.[/QUOTE]

Lets say your session has $userprofile->username as the logged in users name variable.

$query="SELECT * FROM content WHERE username='$userprofile->username'";

There you go. Done and done.

________________________________________
Edit: After carefully reading your question, I have to ask why do you want to compare your two tables? You can do that, but it is a useless and inefficient step. You already have the session linking the two.

@IllufoxauthorApr 12.2005 — #Yes, that's kinda what I have now but it only works because I have the username and password in the "content" table. However, I want it out of the "content" table.

So instead of

$query="SELECT * FROM content WHERE username='$username'";

I need something like this:

$query="SELECT * FROM content, login WHERE username='$username'";

I learned that in ASP but I don't think it works in PHP....

@IllufoxauthorApr 12.2005 — #Just now read your "Edit" note.....so you mean I should use the session ID as the user ID? ....would make sense but currently my user ID is set on "auto increment"....how do I add the session ID instead? Also, doesn't the session ID change with every new session?

Here's my session code:

<?php

$_SESSION['id'] = session_id();

$_SESSION['username'] = $username;

$_SESSION['time'] = time();

?>

@abudabitApr 12.2005 — #No no, not the session id as your userid. When you login, you grab the userid from the userprofile table and put it into a session variable. Then when a user goes to the page that displays his/her info, the page searches for every content row with the userid as the owner. You don't need password in your content table, but you do need the userid or something identifying that unique user (unless you don't want to link it with a user, but that isn't the case here). There is no way around it, and there isn't going to be in any other programming language or database system either.

@IllufoxauthorApr 12.2005 — #How do you 'grab' the user id from the login table?

@IllufoxauthorApr 12.2005 — #How do you 'grab' the user id from the login table. I mean, what code do I add to the session code? Or does that mean that I have to create an additional query that compares the user id to the user name and puts this info into the session?

Sorry, I'm totally confused here....

@abudabitApr 12.2005 — #How do you 'grab' the user id from the login table. I mean, what code do I add to the session code? Or does that mean that I have to create an additional query that compares the user id to the user name and puts this info into the session?

Sorry, I'm totally confused here....[/QUOTE]

Here is me grabbing info from a table:

[CODE]
 $sque = "SELECT * FROM userprofiletable WHERE username = '$loginname' LIMIT 1";
 $result = mysql_query($sque, $link_id);
 $qdata1 = mysql_fetch_object($result);
 
 if ($qdata1->password == "$loginpassword" && other login logic) {
 $userprofile = $qdata1;
 //also I like to modify the users statistics here, like last login and stuff.
 }
 [/CODE]

Where $userprofile is a session variable I created.

The above is from the login page. Below is from the search page.

I just grab the scalar from the $userprofile when it comes time to do the search:

[CODE]
 
 $query="SELECT * FROM content WHERE useridnumber='$userprofile->useridnumber'";
 
 [/CODE]

See?

@IllufoxauthorApr 12.2005 — #Wow, that's way more code than I expected but it makes sense to me. I would not have been able to come up with this. Just need to adjust it so I can use it on my pages.

Thanks for your help!

@abudabitApr 12.2005 — #No problem. And doing things this way lets you easily add new features and new content databases usable by your existing users.

And if security is a big concern since you mentioned it earlier, we can probably help you set up a system where it checks that the Remote IP is the same as the one logged in. Thats about as far as I have ever needed to take security. Usually I don't even do that since most of my coding is based around research and not customers.

@IllufoxauthorApr 12.2005 — #Ok, I've set this all up as a test, but I got some error reportings on the login page.

Here is the login page code:

[code=php]<?php
 session_start(); 
 ob_start();
 include("database.php");
 
 $sque = "SELECT * FROM users WHERE username = '$username' LIMIT 1";
 $result = mysql_query($sque, $link_id);
 $qdata1 = mysql_fetch_object($result);
 
 if ($qdata1->password == "$password") {
 $userprofile = $qdata1;
 include "include/newsession2.php";
 header ('Location:main.php');
 exit();
 
 } else {
 session_unset();
 print "Wrong Login. Use your correct User Name and Password and try again.<br> <input type='submit' value='Retry' onClick='history.go(-1)'>";
 }
 mysql_close();
 ?>[/code]

And here is the error message I get after logging in:

[B]Warning: [/B]mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/illufox/public_html/Clients/login2.php on line 7

[B]Warning:[/B] mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home/illufox/public_html/Clients/login2.php on line 8

[B]Here are Line 7 & 8:[/B]

$result = mysql_query($sque, $link_id);

$qdata1 = mysql_fetch_object($result);

@abudabitApr 12.2005 — #Ahh, good old Warning: mysql_query(): supplied argument is not a valid MySQL. I've seen that many o' times.

There are three common reasons why that appears:

1. You did not properly connect to the database.

2. "Username" is not a column.

3. The data is bad.

Since your data is wrapped in '' we can eliminate #3. I assume username is a column since you are specifying it, so eliminate #2.

How about that $link_id? Does that have a real value? I noticed that earlier on you weren't using it, and I assume you still haven't defined it. If this is the case, leave that optional $link_id out of there. What that second parameter for mysql_query does is specifiy the resource link. If it is left out the query will go to the last database opened. If an empty variable is there, like you probably have, it will point to a non-existant resource - and thus the error.

And the fetch error will go away when you fix the query error.

@IllufoxauthorApr 12.2005 — #

1. I didn't make any changes to the database connection file.

2. "username" is a column (I have three columns: username, password, id)

3. I have been entering the correct user name and password if that's what you mean

So if I understand this correctly you want me to eliminate "$qdata1 = mysql_fetch_object($result);"?

I don't know what $link_id stands for. I've also never seen $sque. I just put that in there because you had it in your example.

Sorry, I'm still very new to PHP/MySQL as you notice...

@abudabitApr 12.2005 — #No no, keep the fetch command. That is fine. Here is your problem:

$result = mysql_query($sque, $link_id);[/QUOTE]

Turn it into

$result = mysql_query($sque);[/QUOTE]

$sque is just a variable to hold your search query. $link_id is a variable I used in my example because my database connection method is different. Don't worry about using it.

@IllufoxauthorApr 12.2005 — #It works!!!!!! ?

Thank you for all your help and patience. You are an awesome teacher! ?

@abudabitApr 12.2005 — #That's great.

With time you can do some awesome things with the use login system, like user preferences tables and stuff.

Also in #PHP _↴

[RESOLVED] Hello i'm new, can somebody please help me?save php image as jpg or png Help with our site

Success!

Help @Illufox spread the word by sharing this article on Twitter...

Tweet This

Values not inserted in database

43 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Values not inserted in database

43 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

43 Comments(s) _↴

Also in #PHP _↴