SitePoint Sponsor

User Tag List

Results 1 to 25 of 25

Thread: mySQL questions

  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    70
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    mySQL questions

    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. #2
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nick2price View Post
    but would I get an error back if I make a mistake like have an incorrect column name in my insert query?
    it depends on how your php code is structured.

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

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    70
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok. I know that my code goes into this function, added echos earlier to check
    Code:
    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
    Code:
    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
    Code:
    <?
    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. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
     
    $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. #5
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    70
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    kool, so the error I get is
    Duplicate entry '1' for key 1

    Now I start of my insert columns like
    Code:
    (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. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,683
    Mentioned
    99 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by nick2price View Post
    the error I get is
    Duplicate entry '1' for key 1

    Now I start of my insert columns like
    Code:
    (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?
    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.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  8. #8
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    70
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Managed to find the sql for the table which is
    Code:
    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. #9
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,683
    Mentioned
    99 Post(s)
    Tagged
    4 Thread(s)
    While I'm no expert at MySQL, the INT(50) seems to be a problem.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  10. #10
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    INT(50) is not the problem

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    INT(50) is not the problem
    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. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    ... I can't see what the x in INT(x) represents.
    want me to look up the page in the manual for you which explains it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks again

    Quote Originally Posted by r937 View Post
    since it's not just you, i'll give you a link to it -- http://dev.mysql.com/doc/refman/5.1/en/
    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

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    everyone working with software should want, need, and know where to get access to the documentation for that software
    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. #19
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    yep, agree and that is why I often post links in my posts to more info because not all noobies know where to look.
    fascinating... and yet ~you~ did not know where to look in the mysql manual

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you are again blatantly not telling the truth.

    I have the url to the manual bookmarked. I just didn't feel like going to look for the appropriate page and that is why I asked you, or anyone else that knew the answer, the question. Sometimes people forget these forums are not a 1 to 1 conversation.

    I have had added in other forums where I post your comment along side the previous post where you haven't told the truth about me

  22. #22
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,683
    Mentioned
    99 Post(s)
    Tagged
    4 Thread(s)
    Okay, enough bickering. Much more and I'll have to wade on through here cleaning up this thread.
    Or in a more polite manner, please take off-thread discussions to a private channel.

    Is the OP's situation resolved?
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  23. #23
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pmw57 View Post
    Is the OP's situation resolved?
    not yet afaik.

    waiting on OP to update his situation.

  24. #24
    SitePoint Enthusiast
    Join Date
    Jun 2010
    Posts
    70
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    lol, sorry, fell asleep. I actually enjoyed the bickering, learned a lot from it. I think I realised what the problem is, as things now to be working. I originally had the ID field set to a default value of 1 (I mistakenly thought this was the starting value). I changed this and everything seems to be good now, well, besides the insert being performed twice each time (this will not be a problem to sort out though).
    Thanks guys

  25. #25
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nick2price View Post
    lol, sorry, fell asleep.
    you picked the right time to nod off

    you didn't miss much

    glad you sorted it out

    time for a


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •