Jump to content

Generting mysql queries based on table data


saynotojava

Recommended Posts

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 ?

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.