Insert Into MySQL via PHP 1

[bodington]

I'm very new to php. I am using php with a mysql database. I am trying to do a simple append query via php. I want to copy the data from one table into another - empty table that has the same structure, then display the new table data in a comma separated list. I'm not sure what I'm doing wrong, but I keep getting error message "unexpected T_String" on the line.. $query = ...

Can anyone tell me what I'm doing wrong with this? Thank you!

Here's my code:

<?php
// Make a MySQL Connection

mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());

//Append the contents of tblOne into tblOnecopy

$result = mysql_query("Select * From 'tblOne');
$row = mysql_fetch_array($result);
while($row = mysql_fetch_array($result)){
$query = mysql_query("INSERT INTO `tblOnecopy` VALUES ($row['Field1'],$row['Field2'],$row['Field3'],$row['Field4']);");
}

//Print out tblOnecopy data separated by commas

$result = mysql_query("Select * From 'tblOnecopy'");
$row = mysql_fetch_array( $result );

// Print out the contents of the table

while($row = mysql_fetch_array($result)){
echo $row['Field1'],",",$row['Field2'],",",$row['Field3'],",",$row['Field4'];
echo "<br />";
}
?>

 

[jet042]

For starters, you don't close the quotes around your select statement. You also only need to assign mysql_fetch_array($result) to $row in the while loop, not before it. Try that and let us know what happens.

 

[bodington]

Ok, I added the ", removed the unnecessary lines, and now I'm getting this error on the same line:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

I really appreciate the help!

 

[jet042]

Take off the semi-colon after $row['Field4']. You don't need it when using the mysql_query() function. Make sure to leave the last one though, as that's the PHP line-end marker.

Also, in your last echo statement, you need to make another change:

while($row = mysql_fetch_array($result)){
    echo $row['Field1'],",",$row['Field2'],",",$row['Field3'],",",$row['Field4'];
    echo "<br />";
}

Should be

while($row = mysql_fetch_array($result)){
    echo $row['Field1'] [red].[/red] "," [red].[/red] $row['Field2'] [red].[/red] "," [red].[/red] $row['Field3'] [red].[/red] "," [red].[/red] $row['Field4'];
    echo "<br />";
}

because . is the concatenation operator for strings in PHP

 

[bodington]

Thank you so much for your help, but I'm still getting the same error on the $query line:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING

(FYI - when I remove that one line, everything else prints fine, so I think it's just that one line that does the append that's messed up.)

Any other ideas??


Here's my code now:
<?php
// Make a MySQL Connection

mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("databasename") or die(mysql_error());

//Append the contents of tblOne into tblOnecopy

$result = mysql_query("Select * From 'tblOne'");
while($row = mysql_fetch_array($result)){
$query = mysql_query("INSERT INTO `tblOnecopy` VALUES ($row['Field1'],$row['Field2'],$row['Field3'],$row['Field4'])");
}

// Print out the contents of the table

$result = mysql_query("Select * From 'tblOnecopy'");

while($row = mysql_fetch_array($result)){
echo $row['Field1'].",".$row['Field2'].",".$row['Field3'].",".$row['Field4'];
echo "<br />";
}
?>

 

[jpadie]

you should not enquote the table name (backticks or otherwise) and you should enquote the field names!

$result = mysql_query("Select * From 'tblOne'");
while($row = mysql_fetch_array($result)){
$query = mysql_query("INSERT INTO tblOnecopy VALUES ('$row[Field1]','$row[Field2]','$row[Field3]','$row[Field4]')");

even then, the query might fail as you should escape each value too.

but ... why are you making it so complex? mysql (recent versions) provides a neat mechanism for these automated inserts

insert into tblOnecopy select * from tblOne

and don't forget your friend create table either

create table tblonecopy like tblone

 

[bodington]

Thank you!!! I used the
INSERT INTO tblOnecopy Select * from tblOne

Works perfect!

I'm a master at making things more difficult than they need to be. This is actually just a small part of a function. Everything else is working, just stuck on this part... trying to get palm pilots integrated to this database - to upload changed data to mysql and got totally stuck on this. Apparantly, my brain has begun to malfunction! Anyway, Thank you Thank you again.

 

[jpadie]

glad to help.

just for the record the long way round looks like this

$result = mysql_query("Select * From tblOne");
while($row = mysql_fetch_assoc($result)){
  $query = '';
  foreach ($row as $fieldName=>$value){
    $query .=  "$fieldName = '".mysql_real_escape_string($value)."', ";
  }
  $query = trim($query,',');
  $result = mysql_query("INSERT INTO tblOnecopy Set $query");
}

or using the VALUES syntax

$result = mysql_query("Select * From tblOne");
$query = '';
while($row = mysql_fetch_assoc($result)){
  $iquery = "(";
  foreach ($row as $fieldName=>$value){
    $iquery .= "'".mysql_real_escape_string($value)."',";
  }
  $iquery .= trim($iquery, ',')."),";
  $query .= $iquery;
}
$sql = "Insert into tblOnecopy VALUES " . trim ($query, ',');
$result = mysql_query ($sql);

the latter has an advantage of executing only one query which will/should translate to a good time saving on the script.

but as posted above, the Insert ... Select syntax is the best option. It is low processor overhead for php (of course) as no recordset is queried and returned, multiple inserts are avoided and the syntax is optimised in mysql.