PHP, MYSQL insert multiple variables

bell · Joined: 16 Mar 2010 Posts: 2

Hi, I am currently building a project that requires tweets to be stored in a database. I am using CURL to read the last 10 tweets (from the .xml file) and echo them on a page, I am also trying to store them in a database if they haven't already been added.

I can get the oldest tweet (from that set of 10) to store in the database, the rest won't.

Here is the code

$query01 = "SELECT * FROM tweets4";

$result01 = mysql_query($query01)or die(mysql_error());

while($row = mysql_fetch_array($result01)){
$latest_id = $row['tweet_id'];
echo '<p>'.$latest_id.'</p>';
}

If ($tweet_id <= $latest_id){
echo "Same number";
}
else {
mysql_query("INSERT INTO tweets4(tweet,tweet_id,user_name,user_id,user_img,time_info) VALUES
('".mysql_real_escape_string($tweet)."',
'$tweet_id',
'$user_name',
'$user_id',
'$user_img',
'$time_info') ")
or die(mysql_error());
}

Just wondering if you could advise me how to store multiple records in a database with one query?

lostboy · Joined: 02 May 2004 Posts: 6033 Location: toronto, canada

mysql has a neat feature which allows you to stack queries for inserts

insert into table (field1, field2, field3)
values('$field1[$x]','$field2[$x]','$field3[$x]'),
('$field1[$x]','$field2[$x]','$field3[$x]'),
('$field1[$x]','$field2[$x]','$field3[$x]'),
('$field1[$x]','$field2[$x]','$field3[$x]')

So you can use php to build the statements up to the 10 that you need and just do one insert

bell · Joined: 16 Mar 2010 Posts: 2

Thanks for the reply.

Just wondering what the php "$x = ?" statement would look like

Sorry its probably a stupid question, I am quite new to php and mysql.

Thanks

lostboy · Joined: 02 May 2004 Posts: 6033 Location: toronto, canada

I was assuming that your data comes in an array. The $x is the row in the array that you are accessing