0

Hello I need some help on a project i am working on.

I have a database with a large amount of records in it. I need to get a number of records into variables to make a graph. Below is part of my code so far.

 //Retrieve all data from the table
 $result=mysql_query("SELECT * FROM solar_panel ORDER BY id DESC") or     die(mysql_error());

$row = mysql_fetch_row($result);
$row1 = mysql_fetch_row($result);
$row2 = mysql_fetch_row($result);
$row3 = mysql_fetch_row($result);
$row4 = mysql_fetch_row($result);
$row5 = mysql_fetch_row($result);
$row6 = mysql_fetch_row($result);
$row7 = mysql_fetch_row($result);
$row8 = mysql_fetch_row($result);
$row9 = mysql_fetch_row($result);
$row10 = mysql_fetch_row($result);
$row11 = mysql_fetch_row($result);
$row12 = mysql_fetch_row($result);
$row13 = mysql_fetch_row($result);
$row14 = mysql_fetch_row($result);
$row15 = mysql_fetch_row($result);
$row16 = mysql_fetch_row($result);
$row17 = mysql_fetch_row($result);
$row18 = mysql_fetch_row($result);
$row19 = mysql_fetch_row($result);
$row20 = mysql_fetch_row($result);
$row21 = mysql_fetch_row($result);
$row22 = mysql_fetch_row($result);
$row23 = mysql_fetch_row($result);
$row24 = mysql_fetch_row($result);

As you can see this will get quiet large, i need to get every 5th row in descending order put into a separate variable but the only way i know is to put all the rows into separate variables then use the 5th one in the graph data.

I would like to do this for about 60 records so to write out all the records i would have to write the above code 300 times. Is there a quicker way to do this.

i have tried doing a loop but can't get the variables to increase with each row selected.

Any help would be much appreciated, or im writing a lot of code

Improve this question
5
  • 2
    Why don't you use array inside your loop. Commented Aug 10, 2013 at 7:40
  • I tried using a count loop array but this did not put each row into a separate variable for me to use. however I am new to PHP and mysql so I might be doing it wrong Commented Aug 10, 2013 at 7:44
  • 2
    That's a very bad approach. You'll end up with an unknown number of variables and you won't be able to loop over them, at least not directly. You'll have to come up with some kind of if (isset($var{$x})). Awful. Commented Aug 10, 2013 at 7:47
  • I tried the following code ( for ($i=1; $i<=100; $i++); { $row$i=mysql_fetch_row($result);} but this just gave an error. Commented Aug 10, 2013 at 7:49
  • It seems noone said this so far: You should use MySQLi or PDO instead of mysql_* functions, which are deprecated. More information avalible here. Commented Aug 10, 2013 at 14:03

4 Answers 4

Reset to default
2

Query only 5th row

If you're only interested in the 5th row, then only request that row from the db:

SELECT * FROM solar_panel ORDER BY id DESC LIMIT 4, 1

Index for LIMIT is zero-based, so the 5th row has index 4.

Query every 5th row

If you want every 5th row then use this more complex query:

SELECT * FROM
   (SELECT *,
           @row := @row + 1 AS rowNumber FROM solar_panel,
           (SELECT @row := 0) AS var
           ORDER BY id DESC
   )
AS ordered WHERE rowNumber % 5 = 0
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I think he wants to get every fifth row (5th, 10th, 15th,...).
@PetrR. then the second query will do
1

Try the following code：

for($i = 0; $i < $count; ++$i){
    $varname = 'row'.$i;
    $$varname = mysql_fetch_row($result);
}

Hope helps!

Improve this answer

7 Comments

I will try and give both a go
Sorry for my ignorance with this but i cant see how this gives me each row into a separate variable. I need to have for example $row1 = row1. $row2 = row2, $row3 = row3, etc: up to say 100. then my graph will request the rows needed to make a graph. In your example above does $$varname increase by 1 each time. thanks
in my loop, each iteration will generate a new variable named row1 to rowcount, why doesn't this meet your requirement?
I think I've got it working with your code. thanks I will have to try it on my graph but looks good so far
@user2669997: don't fetch data from the database you don't need, please check out my answer. While it isn't a drop-in replacement for what you have now, it will be worth the effort.
|
0 
$limit=60;
$fifth=1;
$arrRow=array();
while($row = mysql_fetch_row($result)){
 if(($fifth%5)==0)
 $arrRow[]=$row;
 $fifth++;
 if($fifth==limit)
 break;
}
//now you have every fifth row in $arrRow
foreach($arrRow as $row){
 //handle the data as per your need
}
Improve this answer

4 Comments

80% of the data fetched from the database is unused, what a waste :)
no, am using break; as and when the 60 records gets fetched the while loop gets over
you're throwing away 4 rows to get the 5th, so that's 80%
ohhh, actually i just showed the way to play in his current situation, the best would be to get the modifications done to sql query so that every 5th row comes out as a result and then he just need to run a while loop in a simple manner
0

Do something like this instead:

while ($row = mysql_fetch_row($result))
{
    $rows[] = $row;
}

Then you will essentially have each row in a different variable, except they will all be "grouped together" in the array $rows, allowing you to easily count them, loop over them, shuffle them, and so on. To access them, instead of $row23, you would use $rows[23].

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.