[mfletcher]

hey,

i am developing a simple php/mysql site for a basic library database.

i am having problems in inserting new/updating and deleting records into the books table in the library database. below is my code:

inserting record:

PHP Code:

<?PHP

          include("dbinfo.inc.php");

          $db_con=@mysql_connect(localhost,$username,$password);

          $db_sel=@mysql_select_db($database) or die( "Unable to select database"); 



          $book_id=$_POST['book_id'];

          $title=$_POST['title'];

          $cost=$_POST['cost'];

          $stock_count=$_POST['stock_count'];



          $query_insert="INSERT INTO books VALUES (' ','$title','$cost','$stock_count')";            

          $result=mysql_query($query_insert)or die("Insert Error: ".mysql_error());

          

          mysql_close();

          

          print "Record added\n";

?>

error shown:

Insert Error: Duplicate entry '0' for key 'PRIMARY'

delete record:

PHP Code:

 <?PHP

    include("dbinfo.inc.php");

    $db_con=@mysql_connect(localhost,$username,$password);

    $db_sel=@mysql_select_db($database) or die( "Unable to select database"); 

    

    $book_id=$_POST['book_id'];

    

         $query_delete_id="DELETE FROM  books where book_id=$book_id";

    

         mysql_query($query_delete_id)or die("Delete Error: ".mysql_error());

            

          mysql_close();

      

          print "Record Removed.\n";

?>

error shown:

Delete Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

updating record:

PHP Code:

<?PHP

    include("dbinfo.inc.php");

    $db_con=@mysql_connect(localhost,$username,$password);

    $db_sel=@mysql_select_db($database) or die( "Unable to select database");



    $query_select_id="SELECT * FROM books WHERE id='$id'";



    $result=mysql_query($query_select_id);

    if ($result)

    {

        $num=mysql_numrows($result);

    }



    mysql_close();



    $i=0;

    while ($i < $num)

    {

        $book_id=mysql_result($result,$i,"book_id");

        $title=mysql_result($result,$i,"title");

        $cost=mysql_result($result,$i,"cost");

        $stock_count=mysql_result($result,$i,"stock_count");

        ++$i;

    }

?>

PHP Code:

<?PHP

    print "Enter Line Number to Edit:<input type=text name=book_id size=5>";

    print "<br />";

    print "<input type=submit value=Submit><input type=reset>";

?>

this then gets passed to the change record page

change record:

PHP Code:

<?PHP

    include("dbinfo.inc.php");

    $db_con=@mysql_connect(localhost,$username,$password);

    $db_sel=@mysql_select_db($database) or die( "Unable to select database"); 

    

    $ud_book_id=$_POST['ud_book_id'];

    $ud_title=$_POST['ud_title'];

    $ud_cost=$_POST['ud_cost'];

    $ud_stock_count=$_POST['ud_stock_count'];



    $query_change_selected_id="UPDATE books SET book_id='$book_id', title='$ud_title',cost='$ud_cost' WHERE    id='$ud_id'";

    mysql_query($query_change_selected_id);



    print "Record Updated";



    mysql_close();

?>

but the record does not get changed

I am stuck on what to do here, i have no idea why it does not work.

if anyone could help me that would be great!

cheers

martin

[wirehopper]

Insert Error: Duplicate entry '0' for key 'PRIMARY'
Each book probably needs a unique id.

Delete Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Put the title in quotes, within the string:

PHP Code:

$query_delete_id="DELETE FROM  books where book_id='$book_id'"; 

You shouldn't actually use quotes - use mysql_real_escape_string.

Update:
... id='$ud_id'"; ... there's no variable named ud_id.

[mfletcher]

thanks wirehopper

made the changes but still does not seem to work?!?!?!

would it be possible for you to give me your email address so that i can send you the full pages, may be easier to explain then.

martin

[carters-site]

mfletcher,

You must have book_id set as PRimary Key in your database schema. That is fine but I would make it an AUTO_INCREMENT column so each time you insert the database is automatically adding a new id for you. Otherwise, you will get the error you are seeing.

As far as your SQL statments.

You have $book_id in quotes I assume this is an integer in the database so you should be passing it as one. Though your script is vulnerable to SQL Injection.

I have said this in multiple posts now so sorry to those of you that think I sound like a broken record.
I would recommend using a PEAR:B class to do your queries. Makes DB queries mindless other then writing your SQL and passing the parameters.

[mfletcher]

cheers man,

im going to try that now, iwill keep you all posted with the result,

im new to php/mysql, this is my first attemp at trying it, so i appologise if i sound dumb, and not know exactly what you mean!!

martin