[SOLVED] Moving row to archive table problem

[townclown]

Hi all,

I'm relatively new to php(5)/mysql(5.0) and am having a problem with archiving a record. Entering the information the first time goes without a hitch. However when i want to move the row to an archive table, the single quotes used for apostrophes like " darren's " are throwing off the string used to insert the row. Is there a way around this? I'm rather confused that it inserts the string from the html form without throwing errors(with all the apostrophe's) but when i want to move it again it gives me that error. Here is my rather naive and possibly laughable code. Any help will be greatly appreciated.

 

 

// ***ARCHIVE MATCH REPORT****

if (isset($_REQUEST['deleteid'])){

$id = $_REQUEST['deleteid'];

$str = "SELECT * FROM match_report WHERE match_id = '$id'";

$qry = mysql_query($str);

$array = mysql_fetch_array($qry);

$name =  $array['team_name'];

$opp = $array['opp_name'];

$date = $array['match_date'];

$ko = $array['match_ko'];

$wufares =$array['match_wufa_res'];

$oppres =$array['match_opp_res'];

$report = $array['match_report'];

$goalscorers = $array['match_goalscorers'];

$injuries = $array['match_injuries'];

$venue =$array['match_venue'];

$formation =$array['match_formation'];

 

$copystr = "INSERT INTO arch_match_report (team_name, opp_name, match_date, match_ko, match_venue, match_wufa_res, match_opp_res, match_report, match_goalscorers, match_injuries, match_formation) VALUES ('$team', '$opponent', '$date', '$time', '$venue', '$wufascore', '$oppscore', '$report', '$goalscorers', '$injuries', '$formation')";

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

 

$del = mysql_query("DELETE FROM match_report WHERE match_id = '$id'") or die(mysql_error());

}

 

The error i get is this:

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 's (,)', 'Enter the injured player's names(If any), separated by comma's (,)', '3' at line 1

 

This is a result of the string variable $injuries which contains the apostrophe.

The entry as stored in the injuries field is:

Enter the injured player's names(If any), separated by comma's (,)

As i have said.. inserting it the first time round was not a problem, only when i want to move it to another table do i get this problem.

 

 

Thanks, Darren.

[jeffjohnvol]

Hello Darren,  If the tables are exact and the fields are in the same order, you could try this:

 

mysql_query("INSERT INTO arch_match_report SELECT * FROM match_report WHERE MATCH_ID='$id' ")

 

Jeff

[townclown]

Thanks mate,

That looks a whole simpler as well, will try it now.

Much appreciated.

Darren

[townclown]

Works 100%,

Thanks a million Jeff. You have saved me from tearing any more hair out(Not to mention 20 lines of unnecessary code.

Darren