Trying to Minimize Calls To Database

MySQL database
#1

I am building a custom wallboard system for a restaurant chain in my area. After I got the main system up and running, I am now trying to redesign the system in order to minimize called to the database, and re-structure the data handling to be a bit more efficient.

With that being said, I was able to setup the new database and get it to automatically fill the data I would like. I was also able to pull the data that I need, 12 different rows to be exact. But now I am having a problem with sorting the data to the correct variables to be displayed later in the script.

This is what code I have for this operation so far:

$queryRecords = $conn->query("SELECT * FROM records WHERE store_id = '".$storeId."' order by input_date desc limit 12;") or die();

while ($row = mysqli_fetch_assoc($queryRecords)) {
    $date.$row["record_id"] = $row["date"];
    $team.$row["record_id"] = $row["team"];
    $record.$row["record_id"] = $row["record"];
    print($record.$row["record_id"]);

But when I run this code I get a 500 error in the console, then looking into the error logs, this is what I get:

[Wed Jul 17 18:24:56.068254 2019] [php7:emerg] [pid 1377] [client 73.95.135.142:59969] PHP Parse error: syntax error, unexpected ‘;’ in /var/www/html/t.php on line 21

Any help would be greatly appreciated, I am fairly new to php especially when it comes to arrays.

Thank you in advance!

#2

Before getting into your code attempt we should really start by taking a look at your Database Design. Post or PM an SQL dump of your DB with a few sample records.

Do you have your project on Github? It will make it much easier to review your project as a whole. You can set it to private if you don’t want the world to see the code and just grant access on an individual basis.

#3

Certainly, My database is fairly simple since I am trying to take a more front end approach to the data handling and reduce the back end strain. Here is a manual readout of the table I am trying to access, and a couple sample records

±----------±--------------------------------------------------------------------------------------------±--------±----------±---------±--------------------+
| date | team | record | record_id | store_id | input_date |
±----------±--------------------------------------------------------------------------------------------±--------±----------±---------±--------------------+
| July | ??? | 1600 | mlda | 00000 | 2019-07-17 16:52:00 |
| July | ??? | 130 | mlcc | 00000 | 2019-07-17 16:52:00 |

Every column is an VARCHAR, minus the “input_date” column

#4

Cant be. You have text in two columns.

Lets step back even further. Tell me all about the “custom wallboard system for a restaurant chain”. What is it and what should it do, etc. Think like you were having someone develop it for you and you are describing what you want.

#5

My apologies, I miss-typed.

Also, here is one of the first drafts that I built for the wallboard. But the code is very cumbersome and has a lot of calls to the database. Which is why I am redoing this aspect and trying to make it more dynamic: http://54.205.75.148/

#6

That’s a start, but I am still not sure what this is about. Tell me what it is about, not how you are attempting to do it.

#7

My apologies if I am not being clear, but the main purpose of this project is to provide a telemetry system that collects sales data and displays it for the entire team to see. But since there are multiple different types of data is why I taking this approach. For example, each database entry correlates to a specific record on the front end, which is why they have the “record_id” field. So after I find the most recent data for that store, I need to sort the data based off of this field so it can be used on the front end. Hope that make sense.

#8

We are getting there
, Tell me about this. What is a “Telemetry System”, How would it “collect sales data”? Is it hand entered? Is it connected to something?

What are the types? Are they all from the same source?

#9

Telemetry is an automated communications process by which measurements and other data are collected at remote or inaccessible points and transmitted to receiving equipment for monitoring.

Yes, the data is from another source (the same source), that places it into the database, which is working perfectly as of now knock on wood

And yes, so the different types are the first two screens that display on http://54.205.75.148.

So for example, this record 09%20PM would have the “record_id” of “mda”, so after getting all of the rows from the database, it sets them to variables based on this ID to be used later in the script. But in all actuality even though they are technically different data types, they fit into the same criteria which is why they are in the same table.

#10

Pm me a zip link to all the files and an SQL dump of the DB that I can import. You said you have it working and are trying to optimize it. Perhaps I can grasp what is going on better by seeing exactly what the app is doing.

#11

Some finer points,

SELECT * FROM records WHERE store_id = ‘".$storeId."’ order by input_date desc limit 12;

You want to only select the columns you intend to use, not every column in the table. That will make a small improvement.

Prepared statements. String concatenation takes time, small, but still time. If this query will be run multiple times, it is cached so it takes less time when run again.

What is the goal of the optimization? Is it just less calls or is there more to it than that? Using something like REDIS can greatly improve performance, but it is a waste if your only goal is to go from 10 calls to 2. Which honestly doesn’t matter, and is trying to solve a problem that isn’t one.

#12

Great notes, I’ll look into those changes.

The main goal for the optimization is to make the application more scalable. So once the user logs into the system, they are then directed to this screen where the data that is pertinent to them is displayed, based off of their “store_id” which is attached to their account.

Also, I got the function “working” (I was missing a bracket :roll_eyes:) but when I try to just print the “record_id” it prints the value for “record” any thoughts as to why this might be happening? I think it might be with the way that I am assigning variables, since I have no idea how $record.$row["recordId"] = $row["record"]; would display as a variable

#13

Because you are doing this,

$record.$row["record_id"] = $row["record"];

I don’t know why you are concatenating those variables together though? But that is your issue.

#14

So maybe I am taking the wrong approach to this concept. Is there any suggestions you could make to accomplish pulling the most recent data for a specific Id based on a variable that is set by the logged in user’s “store_id”? For example, a social media site (Facebook, Twitter, ect.,) how would they query all of the a specific users posts and display them based on the account ID or name? (Maybe a bad example, but hopefully you get the point)

This is a sample of the code that is further down on the script where I am trying to use the variables that are set by the php function, if it helps:

<div class="bfast-records"> 
<h2>Breakfast Record</h2> 
<div class="data-fields"><h3>Team</h3>
 <p class="team"><?php echo $teammda; ?></p></div>
 <div class="data-fields"><h3>Dollar Amount</h3> 
<p>$<?php echo $recordmda; ?></p></div>
 <div class="date"><i>Date: <?php echo $datemda; ?></i></div> </div>
#15

You will get substantially better advice if we can view your code as a whole. I will ask one last time to provide the entire application in a zip along with the SQL to re-create the DB. I suspect there are many other problems that need to be addressed.

#16

I would have to agree with you, let me figure out how to get that all together. I’ll send it over shortly.

#17

Your first post mentions arrays, and that is what you should be storing the fetched row(s) of data into. The concatenation syntax you are using, both doesn’t function (would require using variable variables, which are 3 times slower than using an array and results in magically producing variables that you cannot simply search in the code to find the source of) and still has you writing out line after line of bespoke code for each possible column/field. The syntax for adding a single row per id to an array, using an id value as the main array index, would be -

$array_variable_name[$row['record_id']] = $row;
#18

Ok, cool. So then what variable would that produce say the function looked like this $record[$row["record_id"]] = $row["record"]; and the “record_id” is “mcc”? Would it be something like $recordmcc = 1600 or something like this $record.mcc = 1600? I’m just not really positive what the end result looks like when concatenating variables like that.

#19

I have seen the complete code. Here is what you need to specifically do.

  1. Turn on error reporting

  2. Fix the numerous undefined index errors (#5 & #6 will fix that)

  3. Set a Foreign Key on your tables

  4. Use a single query using Prepared Statements with a join to get all your data

  5. Save the query results to an array like so.

    while ($row = mysqli_fetch_assoc($queryRecords)) {
    $data[] = $row;
    }

  6. Stop creating variables for nothing.

  7. Access the Array data as needed like so <?= $data["fastservice"] ?> (Should also use htmlspecialchars)

  8. Put the CSS in its own file and include it in the main file

  9. Put the HTML in its own file and include it in the main file.

  10. Put the images in their own folder

  11. Don’t mess with manually closing the DB connection. Php will do it for you.

This is based on what you currently have. I would suggest using PDO when you can get to learning it.

* Personally, I find the automatic slideshow VERY annoying. If you are going to stick with the slides, I would even put each slide HTML in its own file and include where needed.

The post title is a bit misleading. You only have two queries in the whole thing. A join will get you down to one. What you referred to in the OP as rows should be columns.

When you make the changes, send me the updated code and I will review it.

1 Like
#20

Should I just query the entire table every time, then sort with php? How would I do a prepared statement when the “store_id” will change based on the user?

Sponsor our Newsletter | Privacy Policy | Terms of Service