Scripts Runs without Errors but no Entries in DB?

[BlackRaven]

Ok got the following script, basically what i am trying to do is document all the books, according to the ISBN number, in a seperate table. The script is suppose to do this read the ISBN numer from tblbooks, Check to see If the isbn number does not exist in the bookscatalog table. If it does not, add a new entry with new ISBN, else add a one in the total column for the ISBN number.

<?
include ("connect.php");
$sql="SELECT * FROM tblbooks";
$result=mysql_query($sql);
while($row = mysql_fetch_array($result))
{
$isbn=$row[4];
$sql1="SELECT * FROM bookscatalog LIMIT 100";
$result1=mysql_query($sql1);
while($row1 = mysql_fetch_array($result1)) {
$isbn1=$row1[0];
$num1=$row1[1];
if($isbn==$isbn1){
$num1=$num1+1;
$query23 = "INSERT INTO bookscatalog (total) VALUES ('$num1') WHERE ISBN ='$isbn1'";
}
else
{
$query23 = "INSERT INTO bookscatalog (ISBN,total) VALUES ('$isbn1','0')";
}

mysql_query($query23);
}
}
?>

[dreamcatcher]

Change this line:

mysql_query($query23);

to this:

mysql_query($query23) or die(mysql_error());

and see if you get a sql error.

dc

[Mr_Fern]

There are some things I'd say appear off with your code for your purpose.

One suggestion I offer is that since all you need is the ISBN number from the table books you change your query from "SELECT *" to "SELECT {isbn-column-name}" All that other data is useless to the query, and uses more memory uselessly and will generally cause the program to be slower than it should be.

Also, you'll be querying the bookscatalog way more than you need to be. However many books are found in the tblbooks table, you'll be doing that SELECT, more or less getting the same data. That information should alternatively be queried elsewhere and then documented. Such as the following:

[pseudocode]
Query Catalog for ISBNs

Store found values (i.e. $isbn_exists['$row[0]'] = "exists"; )

Query Books Table for ISBNs

Check ISBNs against array (i.e. $new_isbn = (isset($isbn_exists['$isbn']))? 0 : 1; )

If New ISBN, insert query (i.e. INSERT INTO TABLE [table] (ISBN,total) VALUES ('$isbn','1') )

Else, update query (i.e. UPDATE [table] SET total = total + 1 WHERE ISBN = '$isbn' )
[/pseudocode]

Also, I'm not sure if that LIMIT 100 is such a good idea. If the ISBN does exist in the table but is not located in the first 100 results returned, then it will always try to insert new records for the same ISBNs.

The main thing I wanted to bring to your attention was that the "ISBN already exists" query is invalid. INSERT's create new rows, they do not take WHERE conditions. You would have to put an UPDATE query in place of that one.

The new ISBN insert query (the else) appears to be valid. Although, if you're inserting that ISBN into the catalog, shouldn't the total be at least 1 instead of 0?

Do you have the ISBN column indexed as unique? That would prevent more than one record with the same ISBN.

[BlackRaven]

thanks for the input Mr_Fern, overhauled the whole program and it seems to work now.