mysql while loop?

[tommyboy123x]

I need a table with about 10000 fields, and obviously i cant do this by hand, so i made a while loop

 

<?php
$link = mysql_connect("**********", "*******", "*******") or die(mysql_error());
mysql_select_db("******") or die(mysql_error());

$x = 0;
$y = 0;

mysql_query("CREATE TABLE temp(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(30),
warning INT)")
or die(mysql_error());

while ($x >= 101){
while ($y >= 101){
	$afs2 = 'afs'.$x.$y;
	mysql_query("ALTER TABLE 'temp' ADD '$afs2' VARCHAR(30) NULL DEFAULT NULL") or die(mysql_error());
	$y++;
	}
$x++;
}
echo 'Table Updated!';
?>

 

what is wrong with this..?

[yzerman]

Are you sure you need 10.000 fields?

 

What exactly are you trying to do? Would it be easier to store arrays in one field, and pull those arrays and parse them with PHP?

 

 

[Jessica]

Yeah this is seriously bad database design. Normalize your data!!

[tommyboy123x]

I'm sorry... did I ask for why this is a bad idea?

 

Just because I have a life and don't have thousands of posts doesn't make me an inexperienced programmer - i know what im doing and i have experience enough to know this will work.

 

I just can't figure out why this wont work, and if you can't tell me just dont post for the sake of adding to your pathetic numbers.

 

I assume it is some kind of "overload" but when i try changing the numbers (x >= 1, y >= 10) it still won't work so i assume some kind of error occured.

[JasonLewis]

replace your this:

while ($x >= 101){
while ($y >= 101){
	$afs2 = 'afs'.$x.$y;
	mysql_query("ALTER TABLE 'temp' ADD '$afs2' VARCHAR(30) NULL DEFAULT NULL") or die(mysql_error());
	$y++;
	}
$x++;
}

 

with this:

 

for($x = 0, $y = 0; $x < 100; $x++, $y++){
$afs2 = 'afs'.$x.$y;
mysql_query("ALTER TABLE 'temp' ADD '$afs2' VARCHAR(30) NULL DEFAULT NULL") or die(mysql_error());
}

 

you might need to change the < 100 because i wasnt sure what you actually wanted, but that may do the trick.

[Balmung-San]

I'm sorry... did I ask for why this is a bad idea?

 

Just because I have a life and don't have thousands of posts doesn't make me an inexperienced programmer - i know what im doing and i have experience enough to know this will work.

 

I just can't figure out why this wont work, and if you can't tell me just dont post for the sake of adding to your pathetic numbers.

 

I assume it is some kind of "overload" but when i try changing the numbers (x >= 1, y >= 10) it still won't work so i assume some kind of error occured.

 

Okay, well then take it from the guy with less then 100 posts. It's bad database design. There is no real need for having 10000 different fields. There's probably a better way to design it.

 

What exactly are the 10000 fields being used for?

[JasonLewis]

it would be interesting to find out because the fields would be called something like this:

afs00

afs11

afs22

afs33

afs44

 

etc etc...

 

i think. it would most likely lag the table a lot. there would be no need for it... but its up to the OP, we can only try to convince.

[tommyboy123x]

the for loop above does not work, but it does give me an error at least - says there is a syntax error in the mysql query

 

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 ''temp' ADD 'afs00' VARCHAR(30) NULL DEFAULT NULL' at line 1

 

it also doesnt work because the numbers should be afs01, afs02, afs03, ....afs099, afs010, afs11, afs12, afs13..... afs199, afs20, afs21, afs22, afs23, etc etc

 

 

its for an admin section of a site im working on - it compares all the data within itself but it is almost guarenteed that well over 3/4's of the fields will not be used at a given time.

 

AFS stands for AutoFlag Signature.  An autoflag is given to a user's account when he/she signs up, and they are found to possibly have another account.

 

Each time something suspicous is found as they sign up or log in, it will give an afs, a kind of timestamp, to the user who just signed up and all other users with the same info (ex: same ip address).  The afs in the members table looks something like 475983.523135.537135.537215723.513621.613461, and each one of those numbers is a way to link accounts based on the possible infraction of the ToS.

 

This is a dedicated server for the db and will be accessed not much more than once a day.

[yzerman]

mysql_query("ALTER TABLE temp ADD '$afs2' VARCHAR(30) NULL DEFAULT NULL") or die(mysql_error());

 

This query should get rid of the error, I took the single quotes from around the table name. But as I said earlier, it may be easier to store your data a different way, as your database is going to grow exponentially every time this script is run, and it is only going to work once the way it is currently written because you will be adding the same fields - so when you run the script the second time, you are going to get this error:

 

#1060 - Duplicate column name 'colname'

[JasonLewis]

this list:

it also doesnt work because the numbers should be afs01, afs02, afs03, ....afs099, afs010, afs11, afs12, afs13..... afs199, afs20, afs21, afs22, afs23, etc etc

 

is very confusing. your jumping all over the place, adding 0 is front of stuff. i dont really understand it.

[tommyboy123x]

i know it looks confusing but it goes along with the system set up

 

I changed the "while ($x >= 101){" to "while ($x <= 100){" but it still is not updating the database.

 

It gets the the message and echos "table updated", but the fields are not there, even when i say $x <= 1 and $y <=1 in the while loops

 

The best solution would be to put the while loop inside so it creates the table with all of the fields, but it seems to be difficult because it is in the mysql query  :-\

[sasa]

try

<?php
$link = mysql_connect("**********", "*******", "*******") or die(mysql_error());
mysql_select_db("******") or die(mysql_error());

$x = 0;


mysql_query("CREATE TABLE temp(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
username VARCHAR(30),
warning INT)")
or die(mysql_error());

while ($x <= 101){
        $y = 0;
while ($y <= 101){
	$afs2 = 'afs'.$x.$y;
	mysql_query("ALTER TABLE temp ADD $afs2 VARCHAR(30) NULL DEFAULT NULL") or die(mysql_error());
	$y++;
	}
$x++;
}
echo 'Table Updated!';
?>

remove ' from query

 

btw

This code will stop when $x=11 and $y=1 because afs111 is duplicate ($x=1 and $y=11)

[tommyboy123x]

thanks that did the trick