[SOLVED] How to extract from one table into another

[bri4n]

Hi everybody!

 

I was wondering if someone could give me pointers as to how I can achieve the following.

 

I have data in one table that will be constantly updated by the users. However, at the  beginning of each month I want to take this information and store it in another table. Can anyone give some pointers as how I can achieve this?

 

TIA,

Brian 

 

[MadTechie]

maybe a script to loop throught the records and just insert a new records in the new/archive table, and set that to run once a month in a cronjob!

or

set a timestamp to check to see if the records has been archive and run if it hasn't

[phpknight]

OR you could just empty the table and then do this depending on what your needs are:

 

INSERT INTO table2 SELECT * from table1 where 1

 

MadTechies solution would take more time but could obviously be more robust as well.

[bri4n]

Hey guys!

 

Here is the code I have so far, but it doesn't seem to be behaving:

 

$query = ("SELECT charity FROM userinfo") or die(mysql_error());

$result = mysql_query($query);

 

while ($row = mysql_fetch_array($result)){

$query = "INSERT INTO vote(charity) VALUES $row['charity']" or die ("Could not execute query");

}

echo "Vote section successfully updated!";

 

As you can see the first part extracts the information and stores it in the variable $result, the second part creates an array to store the extracted information and then, theoretically loop through the information and insert it into the new table.

 

However I get the following error message:

 

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in C:\Inetpub\vhosts\god-zone.org.nz\httpdocs\senddollar\vote_update.php on line 15

 

FYI Line 15 in the full code is the following from the above:

 

$query = "INSERT INTO vote(charity) VALUES $row['charity']" or die ("Could not execute query");

 

Any ideas as to what I have done wrong?

 

TIA,

Brian

 

 

[MadTechie]

try this

$query = "INSERT INTO vote (`charity`) VALUES ('".$row['charity']."') or die ("Could not execute query");

[bri4n]

Hi MadTechie!

 

Thanks for the code. That managed to get rid of the previous error message posted in the original posting, but for some reason it doesn't insert the information into the table.

 

Any ideas as to what I may be screwing up? What would you need to help clarify the situation?

 

Thanks for any help!

 

Thanx,

Brian 

[MasterACE14]

with this line:

<?php
while ($row = mysql_fetch_array($result)){

 

try changing it to this:

<?php
while ($row = mysql_fetch_assoc($result)){

 

Regards ACE

[bri4n]

Hi Ace!

 

I changed the line as you suggested, but unfortunately it's still no go...Grrrr

 

Any other ideas as to how to fix this? What do you need from me that may help you diagnose the problem?

 

TIA,

Brian

[roopurt18]

  INSERT INTO vote (charity) SELECT charity FROM userinfo WHERE 1

Try that query instead.

 

Also, in case you keep down the same path.  Your while loop is only assigning to a variable and not actually executing a query.

 

As for your error message, whenever you use a variable in a double-quoted string its a good idea to enclose it in curly brackets.  Try changing the line to this:

$query = mysql_query("INSERT INTO vote(charity) VALUES {$row['charity']}") or die ("Could not execute query");

[yzerman]

<?php
$query1 = ("SELECT charity FROM userinfo;");
$result1 = mysql_query($query);

if ($result) { //did we get a result or did the query fail?
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
     $query2 = "INSERT INTO vote SET charity ='{$row['charity']}';"; //using SET is a much less complicated way of using INSERT when you only have one field to insert.
     $result2 =  mysql_query($query2);
     if (!$result2) {
        echo "There was an error running your MySQL query. The error returned was:<br/>" . mysql_error($result2);
        exit(); //kills the while loop when it receives an error.
    }
  }
echo "Vote section successfully updated!";
} else { //the query failed - why?
   echo "There was an error running your MySQL query. The error returned was:<br/>" . mysql_error($result1);
  exit();
}
?>

[roopurt18]

//using SET is a much less complicated way of using INSERT when you only have one field to insert.

  Unless there is a difference in how MySQL handles each of them, they both seem about the same amount of typing and just as readable to me.

[bri4n]

Hi Guys!

 

I wanna thank-you all for your help. I have some good news and some not so good news.

 

Good news is: We're almost there! (The program seems to extract one row from the first table and insert it into the second table).

 

Bad news is: The program only extracts one row from teh first table and then stops.

 

But...we're on the home straight now!!! Yay! 

 

Here is the code so far:

 

<?php

include("include/assorted.inc.php");

 

/*Connect to database*/

$connection=mysql_connect($host,$user,$password)

or die("Could not connect to the server");

$db=mysql_select_db($database,$connection)

or die("Could not connect to the database");

 

//Retrieve information from database and store result in variable

$query = ("SELECT charity FROM userinfo") or die(mysql_error());

$result = mysql_query($query);

 

while ($row = mysql_fetch_array($result)){

$query = "INSERT INTO vote (charity) SELECT charity FROM userinfo WHERE 1" or die ("Could not execute query");

}

echo "Vote section successfully updated!";

?>

 

Now if someone could just tell me what I need to fix for it to insert every row from the first table, I'll be grateful!! I think it may have something to do with the WHERE 1 part. I tried WHERE * but that didn't help!  :'(

 

TIA,

Brian

 

 

[corbin]

while ($row = mysql_fetch_array($result)){

$query = "INSERT INTO vote (charity) SELECT charity FROM userinfo WHERE 1" or die ("Could not execute query");

}

 

 

THat doesn't execute the query...

 

while ($row = mysql_fetch_array($result)){

$query = mysql_query("INSERT INTO vote (charity) SELECT charity FROM userinfo WHERE 1") or die ("Could not execute query");

}

 

[roopurt18]

There query that I gave you inserts into the table and uses the values to insert as those pulled from the SELECT part.  In other words, the query performs the SELECT and insert and eliminates your need to SELECT and then loop in PHP code.

 

Here is a script that should work:

<?php
  include("include/assorted.inc.php");

  /*Connect to database*/
  $connection=mysql_connect($host,$user,$password)
    or die("Error: Could not connect to the server");
  $db=mysql_select_db($database,$connection)
    or die("Error: Could not connect to the database");

  $sql = "INSERT INTO vote (charity) SELECT u.charity FROM userinfo u WHER 1"
    or die("Error: " . mysql_error());
?>

 

I'll follow up in a moment with some reasons why your other one was wrong.

 

********************

********************

 

Here is the follow up with what was wrong with your previous attempt.

<?php
  include("include/assorted.inc.php");

  /*Connect to database*/
  $connection=mysql_connect($host,$user,$password)
    or die("Could not connect to the server");
  $db=mysql_select_db($database,$connection)
    or die("Could not connect to the database");
  // Everything before this line is OK

  //Retrieve information from database and store result in variable
  $query = ("SELECT charity FROM userinfo") or die(mysql_error());
  $result = mysql_query($query);
  // The above two lines are PROBLEM #1.  You have the right idea, but you need
  // to remove the parens from around the SELECT string, they're not necessary.
  // Next, move the or die() part down to the second line.  Here is how they
  // should look:
  /*
  $query = "SELECT charity FROM userinfo";
  $result = mysql_query($query) or die(mysql_error())
  */


  while ($row = mysql_fetch_array($result)){
    $query = "INSERT INTO vote (charity) SELECT charity FROM userinfo WHERE 1" or die ("Could not execute query");
  }
  // As I explained in my previous post, there is no need to loop anymore.
  // The INSERT ... SELECT ... statement performs both operations at once.  It
  // select data from one table and immediately inserts it into the other,
  // eliminating the need for you to SELECT and then loop in your PHP code.
  // Also, you forgot to call mysql_query() on the query so it wouldn't have
  // ran anyways.

  echo "Vote section successfully updated!";
?>

[bri4n]

Hi Roopert18!

 

Thanks for all your help. I tried the new code that you submitted, but it only extracts the information from the first row of the "userinfo" table. How do I get it so that it extracts the required information from all the rows in the "userinfo" table?

 

Once again, I want to thank you for your help and patience!

 

TIA,

Brian :-)

[roopurt18]

Oh, oops.  I made a mistake.  There is an error in my MySQL query; I misspelled WHERE.  I suspect that it's not inserting any rows and that the row you think its inserting is already present in the table.

 

<?php
  include("include/assorted.inc.php");

  /*Connect to database*/
  $connection=mysql_connect($host,$user,$password)
    or die("Error: Could not connect to the server");
  $db=mysql_select_db($database,$connection)
    or die("Error: Could not connect to the database");

  $sql = "INSERT INTO vote (charity) SELECT u.charity FROM userinfo u WHERE 1"
    or die("Error: " . mysql_error());
?>

[bri4n]

Hi Roopurt18!

 

Just me again! I did changed WHER to WHERE. I thought that maybe the first speelnig might of been some short cut in the code that I didn't know about.

 

But now...the code just doesn't want to put anything into the "vote" table!!  :'( GGrrr...who thought that something sooo simple could produce such a headache!! Any ideas???  ???

 

Again, if there is anything additional that you need to assist me in resolving this issue, just let me know! Here is the code once again, it is pretty much the same as the one you gave me:

 

<?php

include("include/assorted.inc.php");

 

/*Connect to database*/

$connection=mysql_connect($host,$user,$password)

or die("Could not connect to the server");

$db=mysql_select_db($database,$connection)

or die("Could not connect to the database");

 

//Retrieve information from database and store result in variable

$sql = "INSERT INTO vote (charity) SELECT u.charity FROM userinfo u WHERE 1"

    or die("Error: " . mysql_error()); 

echo "Vote section successfully updated!";

?>

 

Do you think that last line is throwing it out? (i.e. the line echoing the vote section successfully updated)?

 

TIA,

Brian

[roopurt18]

Bah, I have brain damage.  I forgot to call mysql_query().

 

Change:

  $sql = "INSERT INTO vote (charity) SELECT u.charity FROM userinfo u WHERE 1"
    or die("Error: " . mysql_error());

 

To:

  
  $sql = "INSERT INTO vote (charity) SELECT u.charity FROM userinfo u WHERE 1";
  mysql_query($sql) or die("Error: " . mysql_error());

[bri4n]

Hi Roopurt18!

 

We're soooo close, I can smell victory  ... I updated the code so now it's:

 

<?php

include("include/assorted.inc.php");

 

/*Connect to database*/

$connection=mysql_connect($host,$user,$password)

or die("Could not connect to the server");

$db=mysql_select_db($database,$connection)

or die("Could not connect to the database");

 

//Retrieve information from database and store result in variable

$sql = "INSERT INTO vote (charity) SELECT u.charity FROM userinfo u WHERE 1";

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

echo "Vote section successfully updated!";

?>

 

When I run it, now I get the following error message:

Error: Duplicate entry '1' for key 1

 

The whole object of this script is to extract one of each charity (I think the DISTINCT option would take care of this, what do you think?) from the "userinfo" table and import it into the "vote" table.

 

TIA,

Brian 

 

 

 

[roopurt18]

The error indicates that the destination table has a unique constraint on one or more columns.  In phpMyAdmin, run the following queries and paste the output:

 

SHOW CREATE TABLE vote;

 

SHOW CREATE TABLE userinfo;

[bri4n]

Hi Roopurt!

 

Here si the output from SHOW CREATE TABLE vote query:

 

Table  Create Table 

vote  CREATE TABLE `vote` (\n  `choice` int(11) NOT NULL default '1',\n  `charity` varchar(50) NOT NULL default '',\n  `votes` int(11) NOT NULL default '0',\n  PRIMARY KEY  (`choice`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

and here is the output from the SHOW CREATE TABLE userinfo query:

 

 

 

[bri4n]

Hi Roopurt!

 

I dunno what happened, I pressed something (it wasn't the "Enter" key) and the damn browser decided to post the partial reply  . So here is the posting in it's entirety:

 

Here is the output from the SHOW CREATE TABLE vote query:

 

vote  CREATE TABLE `vote` (\n  `choice` int(11) NOT NULL default '1',\n  `charity` varchar(50) NOT NULL default '',\n  `votes` int(11) NOT NULL default '0',\n  PRIMARY KEY  (`choice`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

Here is the output from the SHOW CREATE TABLE userinfo query:

 

userinfo CREATE TABLE `userinfo` (\n  `firstname` varchar(50) NOT NULL default '',\n  `lastname` varchar(50) NOT NULL default '',\n  `town` varchar(50) NOT NULL default '',\n  `city` varchar(50) NOT NULL default '',\n  `email` varchar(50) NOT NULL default '',\n  `phone` varchar(20) NOT NULL default '',\n  `charity` varchar(50) NOT NULL default '',\n  `contribute` int(10) NOT NULL default '0',\n  `time` datetime NOT NULL default '0000-00-00 00:00:00',\n  PRIMARY KEY  (`email`)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

Any ideas?

 

Thanks for all your help, I really appreciate your time...

 

Thanx,

Brian

 

[MadTechie]

the problem seam to be choice in vote

`choice` int(11) NOT NULL default '1'

should be

`choice` int(11) NOT NULL AUTO_INCREMENT = 1

 

 

if you use PMA (PhpMyAdmin) change choice to AUTO INCREMENT, and it should be fine

[MadTechie]

full code to create

CREATE TABLE `vote` (
`choice` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`charity` VARCHAR( 50 ) NOT NULL ,
`votes` INT( 11 ) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[bri4n]

Hi MadTechie!

 

Yay...Yippee..Yahoo  !!! That got it, everything works perfectly! I am extremely grateful to all that have helped, especially Roopurt18 and MadTechie! I bow before you in extreme gratitude! 

 

Thank you all for your help and patience in assisitng me with this problem! 

 

Thanx,

Brian