Page 1 of 1
Insert in loop
Posted: Sun Oct 10, 2004 4:09 pm
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++;
}
?>
Posted: Sun Oct 10, 2004 4:10 pm
by jabbaonthedais
I had to change the "i" in "INSERT lNTO" to a L to get it to post.
Posted: Mon Oct 11, 2004 3:19 am
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.
Posted: Mon Oct 11, 2004 3:29 am
by m3mn0n
or die ( mysql_error () )
Pop that into your MySQL query, fetching, and connecting functions.
Posted: Mon Oct 11, 2004 11:06 am
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++;
}
?>
Posted: Mon Oct 11, 2004 5:08 pm
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.
Posted: Sun Oct 17, 2004 6:02 pm
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ї] = 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)) && isset($rowsї$which]));
$sql = 'INSERT ' . 'INTO' . ' zlink1 (oid, date, url, text) VALUES(''' . $rowsї$which]ї'id'] . ''', ''' . $date . ''', ''' . $rowsї$which]ї'url'] . ''', ''' . $rowsї$which]ї'text'] . ''')';
if(!mysql_query($sql)) break;
$insertedї] = $which;
}