Insert in loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Insert in loop

Post by jabbaonthedais »

I'm trying to insert some data to my table in a loop. It inserts the first row, but only one. It should insert 36 rows. I don't see why it isn't working, unless I'm doing something way wrong. I had to convert parts of my array row[] to strings because before it would just post "Array" in my table.

Code: Select all

<?
mysql_connect ('localhost', 'admin', 'password') ;
mysql_select_db ('phpbb');
$date = date("Ymj");
$u = array(1,2,3,4,5,6,7,8,9,10,01,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36);
// shuffle array
srand((float)microtime()*1000000);
shuffle($u);

// 1-36
for ($i = 1; $i <= 36; $i++){
$g = 0;
$result = mysql_query("SELECT id,url,text FROM zlinks WHERE cat=$i");
$cnt = mysql_num_rows($result);
$rw = mysql_fetch_row($result);
$abb = ( $cnt + $rw[0] );
$add = ( $abb - $cnt );
// rand
srand ((double) microtime( )*1000000);
$z = rand($add,$abb);
$t = mysql_query("SELECT id,url,text FROM zlinks WHERE id=$z");
$row[$i] = mysql_fetch_row($t);

$zid[$i] = $row[$i][0];
$zurl[$i] = $row[$i][1];
$ztxt[$i] = $row[$i][2];
$zni[$i] = $u[$g];

$query[$i] = "INSERT lNTO zlink1 (id, oid, date, url, text) VALUES ('$zni[$i]', '$zid[$i]', '$date', '$zurl[$i]', '$ztxt[$i]')";
mysql_query($query[$i]);
$g++;
}
?>
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

I had to change the "i" in "INSERT lNTO" to a L to get it to post.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

why are you doing

Code: Select all

<?php
$query[$i] = "INSERT lNTO zlink1 (id, oid, date, url, text) VALUES ('$zni[$i]', '$zid[$i]', '$date', '$zurl[$i]', '$ztxt[$i]')";
mysql_query($query[$i]);
?>
when

Code: Select all

<?php
$query = "INSERT lNTO zlink1 (id, oid, date, url, text) VALUES ('$zni[$i]', '$zid[$i]', '$date', '$zurl[$i]', '$ztxt[$i]')";
mysql_query($query);
?>
would work without the need for another array.

try rewritting your code without all the arrays at the end unless you need them for another purpose later.

try echo ing out your final query to make sure the data that you want to insert is changing to make sure that it all works and it's just not submitting the same query over and over.
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

or die ( mysql_error () )

Pop that into your MySQL query, fetching, and connecting functions.
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

I saw the problem. I had g=0 after the loop started. But if I use $zni[$i] it skips several rows. Sometimes just one, sometimes 2 or 3. I'm guessing the shuffle($u) isn't stable? Anyone else know how I can add random numbers 1-36 (but not duplicated) as ids for my rows?

The code below works perfectly, except all the rows are in order.

Code: Select all

<?
mysql_connect ('localhost', 'admin', 'password') ;
mysql_select_db ('phpbb');
$date = date("Ymj");
$u = array(1,2,3,4,5,6,7,8,9,10,01,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36);
// shuffle array
srand((float)microtime()*1000000);
shuffle($u);

// 1-36
$g = 0;
for ($i = 1; $i <= 36; $i++){
$result = mysql_query("SELECT id,url,text FROM zlinks WHERE cat=$i");
$cnt = mysql_num_rows($result);
$rw = mysql_fetch_row($result);
$abb = ( $cnt + $rw[0] );
$add = ( $abb - $cnt );
// rand
srand ((double) microtime( )*1000000);
$z = rand($add,$abb);
$t = mysql_query("SELECT id,url,text FROM zlinks WHERE id=$z");
$row[$i] = mysql_fetch_row($t);

$zid[$i] = $row[$i][0];
$zurl[$i] = $row[$i][1];
$ztxt[$i] = $row[$i][2];
$zni[$i] = $u[$g];

$query = "INSERT lNTO zlink1 (oid, date, url, text) VALUES ('$zid[$i]', '$date', '$zurl[$i]', '$ztxt[$i]')";
mysql_query($query);
$g++;
}
?>
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

I still don't understand. I can use the shuffle{} funtion and echo all the numbers fine. It does 1-36 with no problem. But, if I put it in my mysql query, it skips some rows. And if I take out the shuffle, all rows get inserted normally. Any other way to put my rows in a random order in the table? I am inserting 36 rows and want them in a random order.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

ignoring the octal number in line 5 ;)

Code: Select all

$rows = array();
$query = mysql_query('SELECT id,url,text FROM zlinks WHERE cat IS BETWEEN 1 AND 36') or die(mysql_error());
while($rows&#1111;] = mysql_fetch_assoc($query));
array_pop($rows);

$inserted = array();
$size = sizeof($rows);
while(sizeof($inserted) != $size)
{
  while(!in_array($inserted,$which = mt_rand(1,36)) &amp;&amp; isset($rows&#1111;$which]));
  $sql = 'INSERT ' . 'INTO' . ' zlink1 (oid, date, url, text) VALUES(''' . $rows&#1111;$which]&#1111;'id'] . ''', ''' . $date . ''', ''' . $rows&#1111;$which]&#1111;'url'] . ''', ''' . $rows&#1111;$which]&#1111;'text'] . ''')';
  if(!mysql_query($sql)) break;
  $inserted&#1111;] = $which;
}
Post Reply