I manage to create a page where I can slide all my classes data (following these tips). Now I’d like add the students of each class. The class mysql table and the students table are a common field (column): ID_classe.
Firstly I would question your use of LIKE in the query, since you don’t appear to be (intentionally) using any wild-cards with it. LIKE is generally used for searching more ambiguous values, such as in a search function. For finding a precise value use = instead.
The other thing is that the _ underscore character is actually a wild-card when using LIKE though I assume you are using it literally as part of the column name, which isn’t going to help in a LIKE query.
Those two tables should hold the unique/one-time information about classes and students. When you assign students to a class, you would have a class_student table, with columns for an id, class_id, and student_id.
To get the class/student data, you would use a single JOIN query. Do not run SELECT queries inside of loops.
If you find a situation where you need to (this isn’t one of them) the more efficient approach is to prepare the statement before the loop, then execute with any variable parameters bound, within the loop.
thank you, but I use often like instead of =, without problems, so far. And even replacing like with = the problem is still there.
Likewise if I comment ID_classe (I’m not sure if you mean that) nothing changes.
In this case I think you should follow the advice given by @mabismad and use a single query with a JOIN.
I’m actually a bit rusty with mysqli because I have used nothing but PDO for years. I do know that using prepared statements and binding parameters is more awkward and clumsy in mysqli. Hence why I always use PDO instead.
Regarding LIKE Vs =, definitely use =unless you are doing a search with wild-cards. You are looking for an exact match, not “something like”.
$result = mysqli_query($conn, "SELECT * FROM studenti WHERE ID_classe like 'ID_classe'");
were you intending to compare the column ID_classe to the value of $row->ID_classe that you retrieved in the first query, or the actual string "ID_classe" as you are doing? It seems to me (on a quick read only) that as you’ve already closed the first while() loop before you get to that query, you’re always going to be retrieving for the last class in the loop even when you use the variable name rather than the string.
But as mentioned above, if you do want the students in each class, grouped by class, better to do a single query with a JOIN, and have your PHP loop check when the class changes and display the header / new table / whatever.
Pseudo-code
$lastclass = ""
query("select cognome, nome, abitazione, dati_personali, student.ID_classe
from student left join studenti_age on student.ID_classe = studenti_age.ID_classe")
while ($row = fetch) {
if ($row->ID_classe != $lastclass ) {
// display your class title / new table etc.
}
// display student details
$lastclass = $row->ID_classe
}
(You might need an inner join instead of a left join, I’m a bit rusty on it).
As for like vs =, I imagine like won’t be able to use indexes in the same way that = can, so you’ll see better performance with =.
Because the mysqli prepared and non-prepared query programming interfaces are completely different, learning how to use it to do a prepared query is as much work as learning a completely new database extension. If you are going to do this much work learning something, you might as well just learn the simpler PDO extension.
Something else that will help you to be able to design, write, test, and debug code/query(ies), is to separate the database specific code, that knows how to query for and retrieve data, from the presentation code, that knows how to produce the output from the data. The way to accomplish this is to put the database specific code above the start of the html document and fetch the data from any select query into an appropriately named variable, then test and use this variable at the correct location in the html markup.
It’s not code, it’s pseudo-code, as I wrote above it. It’s just intended to show the rough idea of what I’d do, so that you can code it yourself. Apart from the title, you can tell it’s not proper PHP code as none of the lines are terminated properly. I don’t use mysqli as it’s more complicated than PDO, so there was no point me trying to write actual code.
That error means that your query didn’t work, it returned false, but you then passed that into the next function which cannot handle it. Does that query work if you run it in phpmyadmin?
I’m not sure you’re using the same code structure as I was intending. My intention was that $lastclass contains the class-id on the previous iteration of the loop that runs through the query results, so that you can see it has changed and display the new class title / ID / heading, whereas you’re using it for the query. You will need to specify an ORDER BY ID_classe clause to make it work, but get the query working first.
There’s also a lot of difference in the list of columns that you specify in your query, though of course you know your database layout much better than I do. The idea is that you are querying the studenti table as the main table, and JOINing to the studenti__age table based on the common column ID_classe, so you need to specify all the columns that you need from both tables in your query.
You ALWAYS need error handling for statements that can fail. The error you are getting is a follow-on error, because the code continued to run after an earlier error occurred and attempted to use the result from a statement that failed. The easiest way of adding error handling for database statements that can fail - connection, query, prepare, and execute, without adding code at each statement, is to use exceptions for errors and in most cases simply let php catch and handle the exception where php will use its error related settings to control what happens with the actual error information (database statement errors will get displayed/logged the same as php errors.) To enable exceptions for errors for the mysqli extension, add the following line of code before the point where you make the database connection -
This query (in phpmyadmin) works (no errors), but not as expected:
SELECT studenti__classi.ID_classe, classe, anno_nascita, studenti__classi.imagelink, note FROM studenti__classi
left JOIN studenti ON studenti__classi.ID_classe = studenti.ID_classe
Indeed I get as many rows as are the students, but without the (single) students data…
And, as a matter of fact, in the php page I have Undefined property: stdClass::$cognome and so for the other students data (nome, data_nascita, abitazione).
That’s because you didn’t select those fields. I’ve reformatted your query for easy reading
SELECT studenti__classi.ID_classe
, classe
, anno_nascita
, studenti__classi.imagelink
, note
FROM studenti__classi
LEFT JOIN studenti ON studenti__classi.ID_classe = studenti.ID_classe
The code in here works because all of those fields are defined in the SELECT portion of the query
So, to make your results match what you want, you need to add them to the query.
SELECT studenti__classi.ID_classe
, classe
, anno_nascita
, studenti__classi.imagelink
, note
, cognome
, nome
, abitazione
, data_personali
FROM studenti__classi
LEFT JOIN studenti ON studenti__classi.ID_classe = studenti.ID_classe
NOTE: In this instance, I don’t think a LEFT JOIN is appropriate. You should never have rows in your student_classi table that doesn’t have a corresponding studenti record. A simple JOIN (or INNER JOIN to be precise) would be more appropriate, and in fact should perform slightly better.
Yes. In phpmyadmin the query (with INNER JOIN) works, and quite as expected. I mean that mysql should be OK.
But I have to work on the php page: there is a “cross” between php/mysql and javascript that I have to understand better.
You can see the code I have used here, as I already said.
Javascript should guarantee that be visible only one class. But no one is now visible (even though all the names are in the php page source code).
I think that I could resolve this last problem on my own.
Otherwise I will tell you soon.
Thank you!
I’m not sure I understand what you’re saying…if you’re not seeing the student information, then my original reply still applies.
In the sample you provided, the author used a SELECT * which will pull in ALL the fields for the table they’re using as an example.
In your code base, you specified the fields you wanted selected instead of the SELECT *, which is the right approach as it gives you granularity into what you’re selecting. But your select wasn’t complete in your PHP. You were missing all the fields after note, which is why they weren’t showing in the generated output.
So you’re saying that this
$lastclass = mysqli_query($conn,'SELECT ID_classe, classe, anno_nascita, imagelink, note FROM studenti__age inner JOIN studenti ON studenti__age.ID_classe = studenti.ID_classe');
actually contains this and it’s still not showing the values in the html?
