Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question id column causing problems...

    Hi, i'm trying to make a simple content management system for my girlfriends jewellery website. I've managed to get the guts of it working via a tutorial off about.com, but i'm running into problems attempting to add a primary key id column to the SQL table.

    While I can add the column with the primary key, auto increment and not null as I wish and it auto numbers the entries I already have in the database, it seems to stop any further uploads from working, despite being told they have succesful.

    The upload page, which works fine until the id column has been manually added to the MySQL database.

    http://www.riccyd.co.uk/sally/index.php

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>

    <body>

    <form enctype="multipart/form-data" action="add.php" method="POST">
    <table width="600" border="0">
      <tr>
        <td colspan="2"><div align="center"><strong>Create a new item listing</strong></div></td>
      </tr>
      <tr>
        <td width="180">Item Title</td>
        <td width="410"><input name="name" type="text" size="53" /></td>
      </tr>
      <tr>
        <td>Item Description</td>
        <td><textarea name="email" id="textarea" cols="50" rows="5"></textarea></td>
      </tr>
      <tr>
        <td>Item Category</td>
        <td><select name="phone" id="select">
          <option value="Necklace">Necklace</option>
          <option value="Ring">Ring</option>
          <option value="Earrings">Earrings</option>
          <option value="Brooch">Brooch</option>
        </select></td>
      </tr>
      <tr>
        <td>Upload Image</td>
        <td><input name="photo" type="file" size="41" /></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>
          <div align="left">
            <input type="submit" value="Add" />
            </div></td>
      </tr>
    </table></form>
    <hr />
    <h3>Product Information</h3>
    <a href="view.php">View Files</a>
    <?php
    // Connects to your Database
    $db=mysql_connect("localhost""riccyd_co_uk""*******"); mysql_select_db("riccyd_co_uk"$db);

    if (!
    $db) {
        exit (
    'Cannot Connect to MySQL Server, please notify webmaster');
    } else {
        echo 
    '';
        }

    $result mysql_query("SELECT * FROM jewellery"$db);
    $num_rows mysql_num_rows($result);

    echo 
    '<p>You have a total of <b>' "$num_rows" ' </b>items on your website</p>';

    ?>

    <table width="200" border="1">
      <tr>
        <td><strong>Category</strong></td>
        <td><div align="center"><strong>Number of Items</strong></div></td>
      </tr>
      <tr>
        <td>Brooch</td>
      <td><?php

    $result 
    mysql_query("SELECT * FROM jewellery WHERE phone='Brooch'"$db);
    $num_rows mysql_num_rows($result);

    echo 
    '<center>' "$num_rows" '</center>';

    ?>
      <div align="center"></div>  </tr>
      <tr>
        <td>Necklace</td>
        <td><?php

    $result 
    mysql_query("SELECT * FROM jewellery WHERE phone='Necklace'"$db);
    $num_rows mysql_num_rows($result);

    echo 
    '<center>' "$num_rows" '</center>';

    ?></td>
      </tr>
      <tr>
        <td>Ring</td>
        <td><?php

    $result 
    mysql_query("SELECT * FROM jewellery WHERE phone='Ring'"$db);
    $num_rows mysql_num_rows($result);

    echo 
    '<center>' "$num_rows" '</center>';

    ?></td>
      </tr>
      <tr>
        <td>Earring</td>
        <td><?php

    $result 
    mysql_query("SELECT * FROM jewellery WHERE phone='Earrings'"$db);
    $num_rows mysql_num_rows($result);

    echo 
    '<center>' "$num_rows" '</center>';

    ?></td>
      </tr>
    </table>

    </body>
    </html>
    The page that does the work (note i know the variable names aren't very appropriate, but changing them caused me further headaches)

    http://www.riccyd.co.uk/sally/add.php
    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>

    <body>

    <?php

    //This is the directory where images will be saved
    $target "images/";
    $target $target basename$_FILES['photo']['name']);

    //This gets all the other information from the form
    $name=$_POST['name'];
    $email=$_POST['email'];
    $phone=$_POST['phone'];
    $pic=($_FILES['photo']['name']);

    // Connects to your Database
    $db=mysql_connect("localhost""riccyd_co_uk""********"); mysql_select_db("riccyd_co_uk"$db);

    if (!
    $db) {
        exit (
    'Cannot Connect to MySQL Server, please notify webmaster');
    } else {
        echo 
    'Connection success<br />';
        }

    //Writes the information to the database
    mysql_query("INSERT INTO `jewellery` VALUES ('$name', '$email', '$phone', '$pic')") ;

    //Writes the photo to the server
    if(move_uploaded_file($_FILES['photo']['tmp_name'], $target))
    {

    //Tells you if its all ok
    echo "The file "basename$_FILES['uploadedfile']['name']). " has been uploaded, and your information has been added to the directory";
    }
    else {

    //Gives and error if its not
    echo "Sorry, there was a problem uploading your file.";
    }
    ?> <br />
    View your file <a href="view.php">here</a>. Add another listing <a href="index.php">here</a>. 
    </body>
    </html>
    And the page for viewing the results
    http://www.riccyd.co.uk/sally/view.php

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>

    <body>

    <table>
    <?php
    // Connects to your Database
    $db=mysql_connect("localhost""riccyd_co_uk""*******"); mysql_select_db("riccyd_co_uk"$db);

    if (!
    $db) {
        exit (
    'Cannot Connect to MySQL Server, please notify webmaster');
    } else {
        echo 
    '';
        }

    //Retrieves data from MySQL
    $data mysql_query("SELECT * FROM jewellery") or die(mysql_error());

    //Puts it into an array
    while($info mysql_fetch_array$data ))
    {

    //Outputs the image and other data
    Echo "<tr><td><img width=300 height=300 src=http://www.riccyd.co.uk/sally/images/".$info['photo'] ."> <br /><br />";
    Echo 
    "<td><b>Item Title: </b> ".$info['name'] . "<br /> ";
    Echo 
    "<td><b>Item Description: </b> ".$info['email'] . "<br />";
    Echo 
    "<td><b>Item Category: </b>".$info['phone'] . "<br />";
    }
    ?> 
    </tr></table>
    </body>
    </html>

    The MySQL interfaces...



    I don't know what the problem with adding this column is, as it isnt referenced anywhere within the PHP, I didn't think it would affect anything but obviously im wrong....

    Any help would be appreciated, feel free to submit anything in an attempt to work it out.

    Thanks
    Richard
    Last edited by Rithotyn; 02-28-2010 at 11:06 PM. Reason: Missed a bit

  • #2
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    From a quick look over, seems this is your problem, the insert query, you aren't even inserting an id at all. Try this

    PHP Code:
    mysql_query("INSERT INTO `jewellery` VALUES ('$name', '$email', '$phone', '$pic', '')") ; 
    Or even better would be this

    PHP Code:
    $query "INSERT INTO `{{table}}` (`id`, `name`, `email`, `phone`, `pic`) VALUES('', '{$_POST['name']}', '{$_POST['email']}', {$_POST['phone']}, {$_POST['pic']})"
    Customize it to how you need it, but also it's a good idea to have ID as the very first field in your table.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?

  • #3
    New Coder
    Join Date
    Feb 2010
    Posts
    29
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Skip the "id" field in the insert query, as it is auto-incremented.
    But do add the field names in it, like masterofollies suggested:
    Code:
    INSERT INTO `{{table}}` (`name`, `email`, `phone`, `pic`) VALUES(...)
    PS. And. err.. do some input validation here:
    Code:
    $name=$_POST['name'];
    $email=$_POST['email'];
    $phone=$_POST['phone'];
    (like, check for
    Code:
    '
    chars)

  • #4
    Senior Coder rangana's Avatar
    Join Date
    Feb 2008
    Location
    Cebu City, Philippines
    Posts
    1,752
    Thanks
    65
    Thanked 372 Times in 365 Posts
    Learn how to javascript at 02geek

    The more you learn, the more you'll realize there's much more to learn
    Ray.ph

  • #5
    New to the CF scene
    Join Date
    Mar 2009
    Posts
    5
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I hadnt put anything in about the ID because it was auto incremented so i thought it basiclly dealt with it itself.

    Is it because I'm not naming the fields that its having issues when it comes to write the data into the MySQL database?

    Thanks for your help folks.

    Ric

  • #6
    Senior Coder
    Join Date
    May 2005
    Posts
    2,137
    Thanks
    96
    Thanked 72 Times in 72 Posts
    Yeah I just said it needs to be customized, just giving examples.
    Rowsdower! has accused me of having mental problems, and the administrator allowed it. What a great forum huh?


  •  

    Posting Permissions

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