retrieve and insert data

[kyin]

Hi, i'm trying to retrieve some data (eg: Name and Installation Date) from Table A and save them with additional data (eg: number of months) to Table B. 

 

Below is part of the codes:

$query = "SELECT InstallationDate, Name FROM A ";

$result = mysql_query($query)

or die ("could not.");

 

//to display Name, Intallationdate and calculated number of months

 

echo "<table border=\"1\" align=\"center\">";

echo "<tr><th>Name</th>";

echo "<th>Installation Date</th>";

echo "<th>Number of months</th></tr>";

 

 

while ($row = mysql_fetch_assoc($result))

{

$IntDate=$row["InstallationDate"];

$Name=$row["Name"];

 

$today = date("d/m/Y");

 

$date1 = $IntDate;

$date2 = $today; //today date

 

......

        ...... // codes to count the number of months

 

 

 

 

echo "<tr><td>";

echo $row["Name"];

echo "</td><td>";

echo $row["InstallationDate"];

echo "</td><td>";

echo $NumberOfMonths;

echo "</td></tr>";

 

 

$query1="INSERT INTO B " . "(Name,InstallationDate, NumberOfMonths) VALUES ('$Name, $InstallationDate, $NumOfMonths')";

$result=mysql_query($query1);

 

 

}

 

} //end while

 

i got this "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ..." msg when i tried to run this.

 

The table with complete data including the calculated number of months is able to be displayed, the problem occurred when im trying to save it into Table B. i've tried using foreach, or serialize, but none of tht works..

 

Is there any other solution for this? i'm a newbie in PHP and been working on this for days.. thanks in advance

[radalin]

Perhaps you are not connected to the database, check that a connection is active.

 

Also there is a problem with your second query:

 

$query1="INSERT INTO B " . "(Name,InstallationDate, NumberOfMonths) VALUES ('$Name, $InstallationDate, $NumOfMonths')";

 

It wont work as the way you have wantend. It must be like:

 

$query1="INSERT INTO B " . "(Name,InstallationDate, NumberOfMonths) VALUES ('$Name', '$InstallationDate', '$NumOfMonths')";

 

There are additional single quotes. Also you should really think in using escape functions like mysql_real_escape_string.

[kyin]

Thanks radalin for your suggestion on using the escape functions. I believe tht would be needed somewhere in the codes. I've found some references on tht2, thx.

 

Regarding on the error msg, the database is correctly connected. Coz the first data in the table is able to be saved into the database when the error msg is displayed.

 

When i remove the "$query=INSERT...." codes in the while loop, the following table is correctly displayed.

 

Name Installation Date NumOfMonths

Tony 10/09/2008 2

Tony 16/05/2008 6

Tony 03/02/2008 9

Tony 10/09/2004 50

Lee 26/10/2008 1

Lee 03/01/2007 22

Lee 07/07/2008 4

Lee 07/04/2008 7

Lee 01/01/2006 34

However, if the query codes were inserted as in the previous mentioned post, the following error with only the first data in the table was displayed and saved.

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-7\www\nummonth2.php on line 57

 

 

Name Installation Date NumOfMonths

Tony 10/09/2008 2

 

p/s: I'm sorry that i actually missed out the single quotes in the query codes in the earlier post.

 

thanks

[premiso]

You are assining result inside the while loop, thus changing everything.

 

CHANGE THIS
      $query1="INSERT INTO B " . "(Name,InstallationDate, NumberOfMonths) VALUES ('$Name, $InstallationDate, $NumOfMonths')";
      $result=mysql_query($query1);

TO

      $query1="INSERT INTO B " . "(Name,InstallationDate, NumberOfMonths) VALUES ('$Name, $InstallationDate, $NumOfMonths')";
      $result1=mysql_query($query1);

 

That should not mess up the while loop by pulling a different set of results.

[kyin]

Thanks alot premiso! the problem is solved. thanks!