mysql fetch array issue

dr_overload

Can anyone help me with this thanks.

Here's my code

$data = mysql_query("SELECT * FROM users WHERE username = '$username'")
or die(mysql_error());
while($info = mysql_fetch_array( $data ))
{
$match_id=$info['week1_match1_id'];
echo $match_id;
}

What I would like is to set the week1 part of the field name to a variable, as this will change each week, etc.. week2, week3

Rather than keep having to change the field name, can I set a variable inside?

example $info['$variable_match1_id'];

Thanks

Jon

benracer

Would this work...

$info['{$variable_match1_id}'];

dr_overload

benracer thanks for your reply,

unfortunately still giving a blank result, as if that field name doesnt exist.

laserlight

What I would like is to set the week1 part of the field name to a variable, as this will change each week, etc.. week2, week3

This makes your database schema sound suspect. It looks like you have repeating columns. What is your relevant database schema?

Rather than keep having to change the field name, can I set a variable inside?

Yes, of course. The array index here is just a string, and of course you can construct a string with variables.

Would this work...

Not quite. More likely:

echo $info[$variable . '_match1_id'];

But first, let's have a look at the database schema. One should not need to have repeating columns of a "week1", "week2" kind.

dr_overload

Hi laserlight

Yes, i have columns named week1_match1_id, week2_match1_id.

Basically, there are 10 columns from match1_id to match10_id for each week.

Example
week1_match1_id: week1_match2_id

week2_match1_id: week2_match2_id

Is this not the best way to have the DB?

Thanks
Jon

benracer

Sorry - took "$variable_match1_id" as a complete variable not a variable and string.

laserlight

Is this not the best way to have the DB?

Probably not. Without knowing the context of what you are trying to model with the database, I can only suggest that you have two columns instead of ten: one for the week, one for the match.

Incidentally, you might want to read the PHPBuilder article on Database Normalization and Design Techniques.

dr_overload

Yeah thanks to you both the

echo $info[$w . '_match1_id'];

works great.

I would like to take up the point laserlight mentioned about the database schema. What alternative would you suggest? Rather than my week1 week 2 etc...

dr_overload

laserlight wrote:
Incidentally, you might want to read the PHPBuilder article on Database Normalization and Design Techniques.

Thanks laserlight I will read up on that.

The context is, the user has a list of 58 possible matches to select. They must select 10 of these 58 matches. Each match has it's own ID therefore my code places the ID of each selected match into a separate field for each match.

Therefore each user will have 10 separate match ID for each week.

laserlight

The context is, the user has a list of 58 possible matches to select. They must select 10 of these 58 matches. Each match has it's own ID therefore my code places the ID of each selected match into a separate field for each match.

Therefore each user will have 10 separate match ID for each week.

You might have three tables: one for users (with at least a user id column), one for matches (with a match id column and a week column), and another for matches selected (with at least a user id column and a match id column).

As such, the matches selected table will have ten rows for each user for each week, thus recording the ten matches selected by the user for that week.