MySQL query to creating multiple variables

[Texan78]

Hello, 

 

I am trying to update and clean up some MySQL queries I have to condense them into a single query and return variables from the result of each field. A lot has changed since I last used this so I am trying to learn the new methods. 

 

While this works, it is impractical and not current. So I am trying to condense this into a single query as this is currently how I am query it to create variables from the fields for a certain row. For brevity I have only include a few examples. 

$sql = "SELECT gpsStatus FROM streamdb WHERE id = 1";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    $i = 1;
    while($row = $result->fetch_assoc()) {
    $gpsStatus[$i] = $row["gpsStatus"];

   $i++;

    }
}

$sql = "SELECT DisplayName FROM streamdb";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    $i = 1;
    while($row = $result->fetch_assoc()) {
    $DisplayName[$i] = $row["DisplayName"];

   $i++;

    }
}

$sql = "SELECT ChaserLocation FROM streamdb";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    $i = 1;
    while($row = $result->fetch_assoc()) {
    $chaserLocation[$i] = $row["ChaserLocation"];

   $i++;

    }
} 

Now I am trying to take the above and combine it into a single query to return multiple variables from the fields of a single row but, I am having a little trouble as I can't seem to find what I am looking for in the php manual and all the examples I have tinkered with hasn't worked. So how do I need go about taking the code above and condensing it into a single query like I am trying to do below and create multiple variables? 

/* Lets make a connection to the database and check for connection errors */
include('dbconn.php');

/* Lets query the database and return the current values */     

$sql = "SELECT gpsStatus, DisplayName, ChaserLocation, StreamStatus, CurrentViewers, TimeStamp FROM streamdb WHERE id = 1";
$result = $mysqli->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc())
      $gpsStatus = $row["gpsStatus"];
      $DisplayName = $row["DisplayName"];
      $chaserLocation = $row["ChaserLocation"];
      $status = $row["StreamStatus"];
      $totalViewers = $row["CurrentViewers"];
      $timeStamp = $row["TimeStamp"];
}

if (!$sql) {
    echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* close connection */
$mysqli->close();

echo $DisplayName;

In case anyone is curious what the dbconn.php contains it is as follows and it connects fine as I put in a statement to echo on successful connection while I am testing. For privacy I didn't include the actual variable values naturally. 

/* Connect to database */   
     $mysqli = new mysqli($servername, $username, $password, $dbname);
     //check connection
        if($mysqli->connect_errno>0)
          {
           die("Connection to MySQL-server failed!" . $conn->connect_error); 
          } else {
	          echo "Connection Successful";
          }

-Thanks!

[mac_gyver]

your current code is missing a opening {  for the while() loop. this would be producing a php syntax error if you had php's error_reporting set to E_ALL and display_errors set to ON in the php.ini on your development system. if you put these settings into your code, they won't help with php syntax errors in the same file, because your code never runs to cause the settings to be in effect.

 

speaking of (writing about) the while() loop, if you are running a query that you expect to match a single row, don't loop to fetch the results. this is just cluttering up your code. just fetch the row as an array into a variable.

 

speaking of (writing about) variables, what's wrong with an array variable? by creating discrete variables from each element of an array, all you are doing is spending time typing, fixing typo errors, and changing the code every time you change what gets SELECTEd by a query or reuse the code for a different query and also make the same changes to the code that uses the data. you should just fetch the data from the query into one php array variable, then use that array variable everywhere in the rest of your code. Keep It Simple. Programming is already a tedious task. Don't make more work for yourself by typing out line after line after line of code that you must keep editing any time something changes.

 

next, what is this - if (!$sql) { ? the $sql variable is a php string that represents the sql query statement you have built. unless it's an empty string, it will never be false. i suspect this usage is trying to handle errors? if so, you need to use exceptions to handle errors. this will eliminate the need to write logic around every database statement that can fail. your main code will only have to deal with error free database statement execution. if you enable exceptions for the mysqli extension, any error will throw an exception. if you let php handle the uncaught exception, it will use the php error_reporting/display_errors/log_errors settings to determine what happens with the actual error information. for the mysqli extension, if you enable exceptions before you try to make the database connection, any connection error will also throw an exception.

 

to enable exceptions for the mysqli extension, add the following before your connection code - 

$driver = new mysqli_driver();
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // MYSQLI_REPORT_ALL <-- w/index checking; w/o index checking --> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT

Edited October 30, 2016 by mac_gyver

[Texan78]

Quick update, I have been doing more research and reading the manual and I think I have found a solution. It works and while that is great I am not sure if it is correct and current. It is certainly more stream line. Does anyone have any suggestions on a better way or if this is correct?

$sql = "SELECT gpsStatus, DisplayName, ChaserLocation, StreamStatus, CurrentViewers, TimeStamp FROM streamdb WHERE id = 1";
$result = $mysqli->query($sql);

$row=mysqli_fetch_array($result,MYSQLI_ASSOC);
printf ("\n",$gpsStatus = $row["gpsStatus"],$DisplayName = $row["DisplayName"],$chaserLocation = $row["ChaserLocation"],$status = $row["StreamStatus"],$totalViewers = $row["CurrentViewers"],$timeStamp = $row["TimeStamp"]);

if (!$result) {
    echo "Error executing query: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* close connection */
$mysqli->close();

-Thanks!

[mac_gyver]

Does anyone have any suggestions on a better way or if this is correct?

 

 

yes, see the reply posted above your's that the ipb forum programmers Chimps couldn't figure out how to reliably notify you of while you were posting your reply.

 

the code you posted implements some of the things i mentioned, but still has some issues -

 

1) by testing $result after you have tried to fetch data from the query, that code will throw a php error at the fetch statement any time the query fails due to an error. you would need to test $result before you try to fetch any data. however, if you enable exceptions like suggested, you don't need to have any logic in your code testing $result. if there is a query error, an exception will be thrown and the code trying to fetch data will never be executed.

 

2) by hard-coding the echoing of $mysqli->errno  and $mysqli->error in the code, you will expose information in the errors to a visitor. if you use the suggested exception method, what happens with the error information is controlled by php settings, so that on a live server, you would log the information, rather than to display it, simply by changing a couple of php settings.

 

3) the first code has this - $row = $result->fetch_assoc() for the fetch statement. you now have this - $row=mysqli_fetch_array($result,MYSQLI_ASSOC). while both are functionally equivalent, why are you making unnecessary changes and going from simpler to more verbose syntax? Keep It Simple.

 

4) see what i wrote above about not creating discrete variables. your current method has you typing things in the SELECT list, two times in each $some_var = $row['some_column']; assignment statement, and then where you use the data. using the suggested method eliminates all the assignment statements. you would typically fetch the data into a variable named to indicate what the data means. so, rather than $row, you would use something like $stream_data.

 

5) lastly, i didn't write it above, but php closes the database connection for you when the php script ends. unless your code is taking a large amount of time to run after fetching the data from the database, there's no good reason to close the database connection yourself.

 

so, for the last posted code, this is all you would really need -   

$sql = "SELECT gpsStatus, DisplayName, ChaserLocation, StreamStatus, CurrentViewers, TimeStamp FROM streamdb WHERE id = 1";
$result = $mysqli->query($sql);
$stream_data = $result->fetch_assoc();

// just use the elements in $stream_data in the rest of the code

[Texan78]

Thank you for the detailed information. It is very helpful and I will keep it into memory. A lot of it was trial and error from reading php.net and numerous other sites trying to self teach myself. I do like cleaner and simpler methods which is why I am trying to clean this up. 

 

What I am a little puzzled with is the code you posted. How do I know what the variables are so I can use the data in the rest of my code?

[Barand]

In your original code you had

 

$gpsStatus = $row["gpsStatus"];
$DisplayName = $row["DisplayName"];

...

 

How did you know what the variables were?

[Texan78]

In your original code you had

 

How did you know what the variables were?

 

 

Because I created them. 

 

The code suggested suggests I don't have to do that anymore. So then how to I access the data from the fields if there is no variable assigned to it?

[mac_gyver]

there already IS a variable assigned to the data from the fields, the variable you fetched the data into, $row. the code you have been trying/posting is already using elements of the array variable here -> $DisplayName = $row["DisplayName"];. just use $row["DisplayName"] everywhere and forget amount $DisplayName.

[Texan78]

I am sorry but I am having a hard time understanding what you're trying to say. None of that makes any sense to me at all. Using $row["DisplayName"] is not a simplified method versus using $DisplayName in my code nor is that a variable. Maybe you're misunderstanding what I am trying to say.  

 

Is there more to this code that I am missing? 

$sql = "SELECT gpsStatus, DisplayName, ChaserLocation, StreamStatus, CurrentViewers, TimeStamp FROM streamdb WHERE id = 1";
$result = $mysqli->query($sql);
$stream_data = $result->fetch_assoc();

This is the only way I can get it to work for what I am needing. 

/* Lets query the database and return the current values */  
$sql = "SELECT gpsStatus, DisplayName, ChaserLocation, StreamStatus, CurrentViewers, TimeStamp FROM streamdb WHERE id = 2";
$result = $mysqli->query($sql);
$stream_data = $result->fetch_assoc();

printf ("\n",$gpsStatus = $stream_data["gpsStatus"],$DisplayName = $stream_data["DisplayName"],$chaserLocation = $stream_data["ChaserLocation"],$status = $stream_data["StreamStatus"],$totalViewers = $stream_data["CurrentViewers"],$timeStamp = $stream_data["TimeStamp"]);

Edited October 30, 2016 by Texan78

[Jacques1]

*sigh*

 

You realize that $stream_data is an array containing all the stream data, right? This array is all you ever need. Whenever you want to use a particular stream attribute, you just access it within the array:

echo 'The display name is '.$stream_data['DisplayName'];    // in reality, the variable must be HTML-escaped

See? You can use the array elements directly. It's neither necessary nor sensible to copy them into separate variables.

 

If there's some other reason for why you think you need separate variables, please explain that specific reason. Right now, it seems you just don't understand arrays.

[mac_gyver]

Using $row["DisplayName"] is not a simplified method versus using $DisplayName in my code

 

 

while it's slightly more verbose, it has eliminated an entire line of code - $DisplayName = $row['DisplayName']; for each discrete variable. it has also 'encapsulated' all the data for this item under one name, $stream_data. this will eliminate any conflict with the data for other items your application may be creating, such as user data. a user could easily have a piece of data called DisplayName. do you want to spend your time trying to keep track of and debug problems with multiple variables called $DisplayName?

 

nor is that a variable.

 

 

it IS a variable. your code assigned the result of mysqli fetch statement to it. you are referencing elements of it on the right-hand side of an assignment statement when you are creating and assigning values to the $DIsplayName, .... variables. an array is just a variable with more than one dimension. i recommend that you read the introductory sections of the php.net documentation so that you can learn what php variables are. anything that starts with a $ is a php variable.

 

Maybe you're misunderstanding what I am trying to say.

 

 

​we understand what you are doing and what you are saying/writing. we are trying to show you a method that eliminates all kinds of time and problems when writing code and makes the data retrieval code simple and general purpose so that you don't have to spend your time beating out line after line of code and can instead concentrate on getting the program logic do what you want it to do.

 

 

 

printf ("\n",$gpsStatus = $stream_data["gpsStatus"],$DisplayName = $stream_data["DisplayName"],$chaserLocation = $stream_data["ChaserLocation"],$status = $stream_data["StreamStatus"],$totalViewers = $stream_data["CurrentViewers"],$timeStamp = $stream_data["TimeStamp"]);

 

 

if you want to display/dump the entire contents of $stream_data for debugging purposes, this is all you need - 

print_r($stream_data);

lastly, by having the data for any particular item in an array, you are set up for the next step that will further simplify your web coding, of using a template to produce the output from that data. a template system will take an associative array of data as input and populate the corresponding tags/place-holders with the correct data.

[Texan78]

For what I am trying to do this is not simplified, especially when using it in javascript versus using just $variable. Anyways, I am just going to roll with it and learn it as for what I am doing at this very moment it is sleek and compact for making unique variables and prevents duplicates when make multiple queries on a single page.

 

With that said, I am not sure how to put this into HTML like I was doing before with this new method. How would I go about replacing the $CurrentViewers with $ksaunders['CurrentViewers'] as there is two places I need to add it and it's being used in HTML. Can I get an example please on how to properly insert and escape this? 

<div class="right">
   <h3 class="panel-title">
      <?php if ($ksaunders['streamStatus'] == 'true' ) { echo "<span class='label label-success'><i class='fa fa-video-camera' title='Stream Online'></i> LIVE</span> <i class='fa fa-eye' aria-hidden='true' title='Current Viewers $CurrentViewers'> <span style='font-size:12px;font-weight:bold; font-family: arial,helvetica;'>$CurrentViewers</span>"; } else { echo "<span class='label label-important'><i class='material-icons' style='vertical-align:-4px; font-size:14px;' title='Steam Offline'>videocam_off</i> OFFLINE</span>"; } ?></i>
   </h3>
</div> 

Also would this be the proper way to add a space before a variable?

echo ' ' .$ksaunders['DisplayName'];

-Thanks

[Texan78]

Never mind, I got it sorted with the following. 

<div class="right">
   <h3 class="panel-title">
      <?php if ($ncopeland['streamStatus'] == 'true' ) { echo '<span class="label label-success"><i class="fa fa-video-camera" title="Stream Online"></i> LIVE</span> <i class="fa fa-eye" aria-hidden="true" title="Current Viewers ' . $ncopeland['CurrentViewers'] . '"> <span style="font-size:12px;font-weight:bold; font-family: arial,helvetica;">' . $ncopeland['CurrentViewers'] . '</span>'; } else { echo '<span class="label label-important"><i class="material-icons" style="vertical-align:-4px; font-size:14px;" title="Steam Offline">videocam_off</i> OFFLINE</span>'; } ?></i>
   </h3>
</div>

[Jacques1]

You still need the HTML-escaping.

 

For that matter, the “simple” code you had before was just plain wrong.

[Texan78]

I said never mind, I got it sorted. 

 

You can now take your arrogance and excuse yourself from this thread as this is the second time you've talked down to me. 

[Jacques1]

I said never mind, I got it sorted.

 

… which doesn't mean anything. When less experienced programmers think they're done, they've finished half of the job at best.

 

 

 

You can now take your arrogance and excuse yourself from this thread as this is the second time you've talked down to me. 

 

When it takes three(!) people and an entire page to convince you of a simple fact and get to the real problem, then, yes, you might get a snarky comment.

 

You'll make life a lot easier for yourself and everybody else if you just state the problem and accept help. Something like: “I've tried your array approach, but I don't know how to insert array elements into a string. Please explain.” The whole thing would be done in 5 minutes.

 

There's a lot more to say, but you're clearly not ready for any information at this point. Feel free to resume the thread when you are.

Edited October 31, 2016 by Jacques1