Help Assigning Query Rows to Different Variables

[dmhall0]

I have a query that pulls 1 field with 20 rows of data.  I need to assign each row to a different variable so that I can then display them in different locations on a page.

I cannot make each row of data a different field because of other constraints.

My data is very well normalized.

I am using mysqli so something like the old mysql_result would be lovely!

How can this be done without hitting my database 20 times?

Thanks for the help.

[melloorr]

I am confused about what you mean. One field is just ONE little box in a database. You get columns which are like 'username' or something then you get rows which list the values in a row like 'melloorr'. Do you mean column?

[dmhall0]

Sorry.  Yes I mean I get 1 column with 20 rows in my query, need each row assigned to a different variable.

Thanks!

[melloorr]

I am not sure if this will work, but if you put this in a variables file:

 

$query = "SELECT * FROM whatever";
$result = mysql_query($query)or die(mysql_error()); 
while($row = mysql_fetch_assoc($result))
                        {
                          $whatever = $row['whatever'];
                        }
                        

 

Just type in all the variables you need then you can call them when you want them.

 

EDIT

 

Won't work, I do not think it is possible without having something to compare it too.

[melloorr]

if you ad an id to the database, I.E. variable 1 will have id = 1 variable 2 will have id = 2 etc. Then this may work, but bare in mind I have not tested it:

 

$i = 1;
while ($i <= 20)
{
$query = "SELECT whatever FROM whereever WHERE id = '$i'";
$result = mysql_query($query)or die(mysql_error()); 

        $whatever$i = $result;
    
$i++
}

[dmhall0]

Here is my code from the above:

 

$i=1;
  while ($i<=22) {
  $profile = "SELECT u_profile.answer " .
                "FROM questions, u_profile WHERE u_profile.username = '" . $_SESSION['username'] . "'" .
                "AND questions.questionid = u_profile.questionid AND questions.q_order IS NOT NULL " .
                "AND questions.q_order = '".$i."'";
                //"ORDER BY questions.q_order ASC";
  $data = mysqli_query($dbc, $profile) or die("Connection Error3: " . mysqli_error($dbc));
  $profile.$i = $data;
  $i++;
  }

 

When I try this it gets to the line just before this and stops.

I tested my query in MySQL and it gives me exactly what is needed.

Any ideas?

[melloorr]

I don't know sorry. I am not familier with mysqli

[PFMaBiSmAd]

A) Don't put select queries inside of loops. It is extremely inefficient.

 

B) Execute one query that gets all the rows you want in the order that you want them.

 

C) Simply iterate (loop) over the row(s) that the query returns and output the data the way you want it on your page.

 

dmhall0, its likely that your - 'different locations on a page' are actually an ordered list of some kind and you simply need to put the necessary code inside the loop that produces the output that you want. If you do need to do something that requires more processing of the data in order to output it, you would store the data in an array using an index that has meaning for how you would like to access the data.

 

P.S. An array is a perfectly find variable, one with more than one dimension, that is used to store related data.

 

P.P.S. mysql_result is the slowest way of accessing data in the result set since it performs a data seek every time you call it. Probably why a mysqli equivalent doesn't exist.

 

P.P.P.S $profile.$i doesn't reference php scaler variables and the syntax that would is three times slower than using an array. To store the result into an array variable, you would use $profile[$i]

[dmhall0]

PFMaBiSmAd - Thanks for the great reply!

Yes, all my data is related.  They are answers to a form; so I am trying to get them to reappear after the user has submitted them.  My form is a mix of static text, checkboxes, and dropdowns so making that "dynamic" from a query out of mysql is a bit above my skill level at the moment.

I got it to work, but I am running 22 queries!  I know, not the most efficient way AT ALL, but it works and I have been struggling with this one for quite some time.

Any suggestions are much appreciated.

[mikosiko]

post your relevant code to get more help