Generting mysql queries based on table data

[saynotojava]

So, i have following code:
 

    $conn = new mysqli($servername, $username, $password, $dbname);
$result = $conn->query("SELECT COUNT(`Device`) AS 'KU' FROM `table` WHERE  `Device` LIKE '%android%'"); 
  if ($result->num_rows > 0) {
	    while($row = $result->fetch_assoc()) {
     {
		  echo "<font size=\"6\">
  <div align=\"left\">Overall number of android devices is  {$row['KU']} </div></font>";
		    } } 
	   }
	   else {}

$conn = new mysqli($servername, $username, $password, $dbname);
$result = $conn->query("SELECT COUNT(`Device`) AS 'KU' FROM `table` WHERE  `Device` LIKE '%ios%'"); 
  if ($result->num_rows > 0) {
	    while($row = $result->fetch_assoc()) {
     {
		  echo "<font size=\"6\">
  <div align=\"left\">Overall number of ios devices is   {$row['KU']} </div></font>";
		    } } 
	   }
	   else {}

 

 

What this is doing, is counting entries which are listed as android and as ios. But how do i make this code blocks based on external data? Let's say i have table in mysql, which contain titles android,ios and windows, and as result i get output which would be matching 3 code blocks, each with query android,ios and windows. Would be putting this code into another while loop be enough ?

 

[saynotojava]

Yes, just tested it and it seems php code/query can be looped inside while loop. Tho, it seems it does not load second entry in table, only first one.

Edited July 20, 2018 by saynotojava

[saynotojava]

Working now, was loading wrong query in second loop. This thread can be closed.

[requinix]

1. Why do you have multiple places in your code setting up $conn? It should happen in one single place.
2. Storing multiple values in the one Device column is bad. Don't do that. What if there's a new device called "RaspberryPiOS"? If you learn about normalization then you'll know you need to be using a secondary table for this.
3. If you're going to use a loop then use a prepared statement. It's more efficient to prepare one SELECT query and then tell MySQL the different values you want to use (the value being the search term).
4. But don't use a loop. You can most definitely check for multiple search terms in one query. Try to find out how.

[saynotojava]

6 hours ago, requinix said:

1. Why do you have multiple places in your code setting up $conn? It should happen in one single place.
2. Storing multiple values in the one Device column is bad. Don't do that. What if there's a new device called "RaspberryPiOS"? If you learn about normalization then you'll know you need to be using a secondary table for this.
3. If you're going to use a loop then use a prepared statement. It's more efficient to prepare one SELECT query and then tell MySQL the different values you want to use (the value being the search term).
4. But don't use a loop. You can most definitely check for multiple search terms in one query. Try to find out how.

1. I was copy paste-ing example code so that line was copied too. I always have it only once.

2.This is how i have it stored - ID 1 Android ID 2 Iphone ID 3 Raspberry

3.Yes it is, not just for that but also to prevent sql injection. Tho, first i want to finish my project , then i will go over entire code and switch all querys with prepared statements.

4. I dont see how at the moment. Tho, i should find it step by step.

And here is sample of current working code:

$conn = new mysqli($servername, $username, $password, $dbname);
$result = $conn->query("SELECT temp FROM temp "); 
 
	    while($arow = $result->fetch_assoc()) {
     {
		
	$bresult = $conn->query("SELECT COUNT(`Device`) AS 'KU' FROM `table` WHERE  `Device` LIKE '%{$arow['temp']}%'"); 
	    while($row = $bresult->fetch_assoc()) {
   
		  echo "<font size=\"6\">
  <div align=\"left\">Overall {$arow['temp']} : {$row['KU']} </div></font>";
		   } 
	   }
	   
	   }

Also, what i noticed, how i using if ($result->num_rows > 0) on almost every query, yet seems to me how that might be not needed in most cases.

Edited July 21, 2018 by saynotojava