MySQL Query with Returned Variables associated with Iterative Numeric Variables For-Loops

Question

I'm trying to dynamically return rows from the database with the variable from each row being associated with a numerically named variable. I have been able to code like the following but is there a way to do this with For Each or something similar?

$sql1 = "SELECT rate, date FROM MyTable WHERE number='1'";
$result1 = mysqli_query($con,$sql1) or die ("Query error: " . mysqli_error());
while($row1 = mysqli_fetch_assoc($result1)) { 
    $rate1=$row1['rate'];
    $date1=$row1['date'];
}


$sql2 = "SELECT rate, date FROM MyTable WHERE number='2'";
$result2 = mysqli_query($con,$sql2) or die ("Query error: " . mysqli_error());
while($row2 = mysqli_fetch_assoc($result2)) { 
    $rate2=$row2['rate'];
    $date2=$row2['date'];
}

Ie like the following:

    $sqlALL = "SELECT rate, date FROM MyTable";

This is where arrays are better suited. Something like `$rate[$row1['number']] = $row1['rate'];` — Nigel Ren, Dec 10 '20 at 16:04
Not sure to understand what you want. Basically you want to have variables $rateX and $dateX, X being mathcing a value from your database response? — olibiaz, Dec 10 '20 at 16:08
You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Dec 10 '20 at 19:33

score 0 · Answer 1 · edited Dec 10 '20 at 19:33

0

Instead of using number variables, using arrays make this much simpler (IMHO). So fetch the number from the table and use this as the index to a results array...

$sql1 = "SELECT number, rate, date FROM MyTable ORDER BY number";
$result1 = mysqli_query($con,$sql1);
$rate = [];
$date = [];
while($row1 = mysqli_fetch_assoc($result1)) { 
    $rate[$row1['number']]=$row1['rate'];
    $date[$row1['number']]=$row1['date'];
}

or index them by a row at a time...

$sql1 = "SELECT number, rate, date FROM MyTable ORDER BY number";
$result1 = mysqli_query($con,$sql1);
$results = [];
while($row1 = mysqli_fetch_assoc($result1)) { 
    $results[$row1['number']]=$row1;
}

edited Dec 10 '20 at 19:33

Dharman

30,962
25
85
135

answered Dec 10 '20 at 16:11

Nigel Ren

56,122
11
43
55

how do I end up with $rate1, $date1, $rate2, $date2 from that? – quick_learner42 Dec 10 '20 at 17:14
You don't, instead of `$rate1` you would use `$rate[1]`. So you have 1 variable and use the number as the index. – Nigel Ren Dec 10 '20 at 17:46

MySQL Query with Returned Variables associated with Iterative Numeric Variables For-Loops

1 Answers1