Read and write to same table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tom91
Forum Newbie
Posts: 8
Joined: Thu Sep 01, 2005 3:10 pm

Read and write to same table

Post by tom91 »

Hi,
I have got the following code that is supposed to take each record from the table, split up the dd/mm/yyyy date into three variables and add a username, then add these back to the same table:

Code: Select all

<html>
<head>
<basefont face="Arial">
</head>
<body>

<?php

// set database server access variables:
$host = "localhost";
$user = "****";
$pass = "******";
$db = "tops";

// open connection
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

// select database
mysql_select_db($db) or die ("Unable to select database!");

// create query
$query = "SELECT * FROM sighting1";

// execute query
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// see if any rows were returned
if (mysql_num_rows($result) > 0) {
    // yes
    // print them one after another

    while(list($number, $date, $location)  = mysql_fetch_row($result)) {
        
          list($day, $month, $year) = split('[/.-]', $date);
       $sluser = "tom91";
        //................................................................................................//
        
         // open connection
    $connection1 = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

    // select database
    mysql_select_db($db) or die ("Unable to select database!");

    // create query
    $query = "INSERT INTO sighting1 (number, date_seen, location, day, month, year, user) VALUES ('$number', '$date', '$location', '$day', '$month', '$year', '$sluser')";

    // execute query
    $result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

    // print message with ID of inserted record
    echo "New record inserted with ID ".mysql_insert_id();

    // close connection
    mysql_close($connection);
 
    }

}
else {
    // no
    // print status message
    echo "No rows found!";
}

// free result set memory
mysql_free_result($result);

// close connection
mysql_close($connection);

?>

</body>
</html>
It alsways comes up with these errors:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\server\Apache2\htdocs\tops\dateup.php on line 32

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in C:\server\Apache2\htdocs\tops\dateup.php on line 79
I'm not sure what these mean or whatcouldbewrong with the code. Any ideas?
Thanks,
Tom
Last edited by tom91 on Wed Jul 05, 2006 10:34 am, edited 1 time in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

firstly.. remove your DB username and password :p

second.. you are establishing a connection twice, no need for this. You can execute mysql_query() multiple times using the same connection.

Try restructuring so you only use one connection.

Thridly - the use of mysql_close() is not essential when the connection is opened with mysql_connect (as opposed to mysql_pconnect()) and I usually find they always error when used like this, but this is one of the valid situations to use the error suppressor operator ('@') IMO. So add:

Code: Select all

@mysql_close($connection);
Post Reply