mySQL questions

Databases
1

Hi. Just got a couple of quick questions about php with mySQL.

Firstly, if I perform a query, I know I can get an error back if there is an issue with the connection, but would I get an error back if I make a mistake like have an incorrect column name in my insert query? Everything seems to be happening for me, no error or anything, but data not going into table (I have checked that connection is good, and variables I am inserting hold values).

Secondly, could sessions cause an issue with performing an insert statement?

Reason I ask the second question is because once in a while, it will allow me to insert a record, but normally only if my table is empty.

any advise appreciated

2

it depends on how your php code is structured.

if you post your code it will be easier to help.

3

Ok. I know that my code goes into this function, added echos earlier to check

function registerUser($firstName, $surname, $email, $username, $password){
   $md5pass = md5($password);
   $_SESSION['regFirstname'] = $firstName;
   $_SESSION['regSurname'] = $surname;
   $_SESSION['regEmail'] = $email;
   $_SESSION['regUsername'] = $username;
   $_SESSION['regresult'] = addNewUser($firstName, $surname, $email, $username, $md5pass);
   $_SESSION['registered'] = true;
}

addNewUser is called by one of the sessions, which is this

function addNewUser($firstName, $surname, $email, $username, $md5pass){
 echo '<ul class="error">';
   echo "<li>ENTER ADD NEW USER!</li>";
   echo "<li>".$firstName."</li>";
   echo "<li>".$surname."</li>";
   echo "<li>".$email."</li>";
   echo "<li>".$username."</li>";
   echo "<li>".$md5pass."</li>";
   echo "</ul>";
  global $conn;
  $q = "INSERT INTO users (firstName, surname, email, username, password) VALUES ('$firstName', '$surname', '$email', '$username', '$md5pass')";
  return mysql_query($q,$conn);
}

All the echos echo out the correct values, so I know these are all holding values.

At the top of this page, I include my Database.php. This performs a standard connection

<?
include("constants.php");
$conn = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
if (!$conn)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db(DB_NAME, $conn) or die(mysql_error());
?>

constants.php holds my details. Connection returns no errors, and code goes into this file as I also tested this.

Reason the result of my qyery is assigned to a session is because I then use this session to decide what is displayed in this page. But even doing this, the session should not really effect the execution of the query.

One point to note is that the first column in my table is an ID with an auto increment. Didnt know if I needed to account for this in the query?

cheers

4

you don’t have to account for the auto-increment in the query if the number of column names in the query matches the number and order of the inserted values in the query.

to output query executuion errors you can use something like

 
$query = "insert........................";
 
if(!mysql_query($query,$conn)) {
       echo 'query = '.$query.'<br />';
       echo 'Error - '.mysql_error();
       die();
}

for inserts, mysql_query() returns true on success or false on errors

5

kool, so the error I get is
Duplicate entry ‘1’ for key 1

Now I start of my insert columns like

(firstName, surname, email ...

In the database though, the first column is ID, and this is auto increment and a primary key. Would it be this causing the issue?

6

now we would really need to see the sql statement that you used to create the table so we can see the table column names, column types and what primary and/or other keys you have set.

7

No, that’s not causing the issue. Unspecified columns just receive their default value.

Is the table empty?

Can you please post the structure of the table that you’re attempting to work with.

8

Managed to find the sql for the table which is

CREATE TABLE `users` (
`ID` INT( 50 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`firstName` VARCHAR( 50 ) NOT NULL ,
`surname` VARCHAR( 50 ) NOT NULL ,
`email` VARCHAR( 70 ) NOT NULL ,
`username` VARCHAR( 50 ) NOT NULL ,
`password` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM

And the error is what was stated in my last post.

Cheers

9

While I’m no expert at MySQL, the INT(50) seems to be a problem.

10

yep, that int(50) could be a problem but I don’t know for sure.

I usually use int(11) or just INT which defaults to INT(11) after the table is created in my database.

now, either that int(50) is an issue and/or somehow you already have a record whose id column = 1 or your table has multiple records with id = 1.

What I would do in this case after removing any duplicate id rows is

  1. use your SQL gui (phpMyAdmin, SQLyog or whatever) and export that table as sql statements and data in the 1 export file which should be just a plain text file containing the sql to create the table and insert statements to insert the current data.

  2. in the export file, change the INT(50) to just INT

  3. run the exported sql script to recreate the table and reinsert the current data.

then run your php script and all will be well (fingers and toes are firmly crossed)

11

INT(50) is not the problem

INT(50) holds exactly the same range of numbers as INT(2) or INT(4) or INT(937)

:slight_smile:

12

so just out of curiosity, what does the number in the () for an INT data type represent, if by the sounds of it, it can be anything.

I know that int, bigint, tinyint etc all have different ranges of integers they can store so I can’t see what the x in INT(x) represents.

13

want me to look up the page in the manual for you which explains it?

:smiley: :smiley:

14

if it’s an online manual, just the link to it will be fine.

I’m sure there could be others reading this as well that could be interested in it.

thanks :slight_smile:

15

everyone should have quick access to the manual, so in this case, since it’s not just you, i’ll give you a link to it – http://dev.mysql.com/doc/refman/5.1/en/

down the right side are links to the individual chapters in the manual

what you’re looking for is in chapter 10, section 10.2, numeric types

:cool:

16

thanks again :slight_smile:

that’s why I put

I’m sure there could be others reading this as well that could be interested in it.

because, apart from being true for any posts in these forums, if I didn’t remind you of that fact I doubt very much I would have got a reply from you :wink:

17

everyone working with software should want, need, and know where to get access to the documentation for that software

i made an exception for you :smiley: :smiley:

18

yep, agree and that is why I often post links in my posts to more info because not all noobies know where to look.

but I think we are now digressing completely from the OP’s intention for his thread.

19

so, getting back to the purpose of this thread, there is no need to necessarily export the table and recreate it.

all you need to do is first verify if in fact you have duplicate id’s in your table and/or whether you have a record with id = 1 already in the table.

20

fascinating… and yet ~you~ did not know where to look in the mysql manual :smiley:

sorry for dragging this on and on, but sometimes i let you have the last word, and sometimes i don’t

:cool: