Setting variables using a while loop

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jamesf89
Forum Newbie
Posts: 1
Joined: Fri Jan 14, 2011 7:47 am

Setting variables using a while loop

Post by jamesf89 »

Now I'm not sure if I have run into a wall because I do't understand enough PHP, SQL or am simply approaching the problem completely wrong. A rough hack of the code I am using is:

Code: Select all

$users = "harry, sally, betty, jim";
$post = " ' \".  $_POST['harry'] . \"  ',' \" .  $_POST['sally'] . \"  ',' \" .  $_POST['betty'] . \"  ',' \" .  $_POST['jim'] . \" ' ";
$query = "INSERT INTO groups (" . $users . ") VALUES (" . $post . ")";
so if I was to

Code: Select all

echo $query;
It would return:

Code: Select all

$query = "INSERT INTO groups (harry, sally, betty, jim) VALUES (' ".  $_POST['harry'] . "  ',' " .  $_POST['sally'] . "  ',' " .  $_POST['betty'] . "  ',' " .  $_POST['jim'] . " ')
So my problem is setting $users and $post. I can use a while loop such as

Code: Select all

$users1 = "SELECT users FROM people WHERE " . $_COOKIE['user'] . " = '1'";
$users1_list = mysql_query($users1);
while ($show_user = mysql_fetch_assoc($users1_list)) {
     echo $show_user['user_list'] . ", ";
}
to return the string:

harry, sally, betty, jim

The core of my question is whether it is possible to use the while loop to set the $user variable? Is there a php function I am unaware of yet?

Thanks
Last edited by Benjamin on Fri Jan 14, 2011 8:28 am, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Setting variables using a while loop

Post by Jade »

The problem is your table's design. You should have one column for the name of the person and another column you use to put your value into.
So your groups table will end up looking like:

[text]
====================
| GROUPS |
====================
| Username | Value |
====================
| Harry | abc |
| Sally | blah |
| Betty | hey |
| Jim | bye |
====================
[/text]

Then you can run a query like:

Code: Select all

mysql_query("INSERT INTO groups (username, value) VALUES ($_POST['username'], $_POST['value'])
or die (mysql_error());
If you want to insert one row for each person in your users table you would do something like this:

Code: Select all

$loop  = mysql_query("SELECT username, value FROM people")
or die (mysql_error());

while ($row = mysql_fetch_array($loop))
{
     mysql_query("INSERT INTO groups (username, value) VALUES ($row['username'], $row['value'])
     or die ('mysql_error');
}
Post Reply