0

I have the following sql table

 imgname  | extension | project
 ---------|---------------------
 img1     | jpg       | testproject
 img20    | jpg       | testproject
 img31    | jpg       | testproject
 ...

I want to retrieve the values of "imgname" and "extension" of "project" "testproject" and store it in separate php variables. Since the count of the rows in the table is variable, i would need to add them dynamically or use arrays in the fetch result. But how do I do that?

My sql/php knowledge has brought me only this far:

$sql=mysqli_query($con,"SELECT imgname FROM images WHERE project='testproject'");
$row=mysqli_fetch_array($sql);
$imagename= $row['imgname'];
ADyson
  • 57,178
  • 14
  • 51
  • 63
nickadams77181
  • 13
  • 3
  • What you are doing should work, so what's the problem? Are you asking how to select multiple columns? Just separate them with a comma. – Dharman May 10 '21 at 08:25
  • @Dharman I think they're confused about what to do when the query returns more than one row. – ADyson May 10 '21 at 08:27
  • Then we have answers like that already – Dharman May 10 '21 at 08:29
  • Read this also https://phpdelusions.net/mysqli_examples/select – Dharman May 10 '21 at 09:05
  • If you are only starting to learn PHP then you should learn PDO instead of mysqli. PDO is much easier and more suitable for beginners. Start here https://phpdelusions.net/pdo & https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection – Dharman May 10 '21 at 09:05

1 Answers1

0

You're correct, you could use arrays. Depending on how you want to use the data afterwards, there are (at least) two potential structures:

  1. Three separate arrays - one list of image names, one of extensions and one of projects.

  2. A single array containing one entry for each row retrieved from the database, with the fields for that row in an associative array (or object) within that.

In most cases, option 2 is likely to be more useful, since it doesn't de-couple the data from the other data items which belong with it logically. (e.g. it would probably be a bit useless to have the extension but not have the file name which went with it. You could try to match it up via the array index, but then you think - why separate it to begin with?)

So here's an example of how you'd implement option 2. Note the use of mysqli_fetch_assoc instead of mysqli_fetch_array, so that you get each row out as an associative array with column names for the keys, instead of just numbers.

$sql = mysqli_query($con,"SELECT imgname FROM images WHERE project='testproject'");
$data = array(); //create an empty array

//loop over all the rows
while ($row = mysqli_fetch_assoc($sql)) {
  $data[] = $row; //add the whole row to the array
}

var_dump($data); //debugging output, just so you can see the result

Alternatively you could also use mysqli_fetch_all to simply get all the results into an array at once:

$data = mysqli_fetch_all($sql, MYSQLI_ASSOC);
ADyson
  • 57,178
  • 14
  • 51
  • 63
  • To be honest I don't find this answer helpful at all. Why suggest decoupling values, why suggest the weird while loop syntax, why not just use fetch_all? – Dharman May 10 '21 at 08:32
  • @Dharman I quite strongly suggested **not** decoupling values. I listed the option to give a comparison, because the OP was mentioning storing the different fields in "separate variables", so I wanted to explain clearly, by example, why that might not be a good idea in most cases. (But equally you can't argue there are 0 situations where someone might want to do that.). `why suggest the weird while loop syntax` ...what's "weird" about it, in your opinion? People do this all the time - perhaps more so if they want to alter the structure of the output array during processing, but still. – ADyson May 10 '21 at 08:45