I’d like use a php function to to recall with the minimal code the code structure for different kind of films. The php is in local, so the aim is not (mainly, at least) security, but what I said: a minimal code.
I have a mysql table films__olon, with many column, in particular “genere”,“titolo”,“contenuti”.
My aim is to organize in the php the films according to its genre (genere), so that I get somthing like the following:
<h2>fantasy</h2>
<h3>Peter Pan</h3>
<p>some content....</p>
<h3> The Wizard of Oz</h3>
<p>some content....</p>
and so on
<h2>science fiction</h2>
<h3> When worlds collide</h3>
<p>some content....</p>
and so on
I tried with this code, but unsuccessfully:
if(isset($_GET['genere'])){
$genus =$_GET['genere'];
}
$stmt = $mysqli->prepare("SELECT * FROM films__olon WHERE genere = ?");
$stmt->bind_param("i", $genus);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while $row = $result->fetch_assoc() {
echo "<h2>$genus</h2>";
echo "<h3>$row[titolo]</h3>";
echo "<p>$row[contenuti]</p>";
}
Of course the database (containing the table films__olon) is already connected.
Is it correct that you’re binding $genus with an integer field type? From your post it seems that might contain a string. It really should contain a number in a normalised database, and the name of the genre should be retrieved from another table. That might be what you’re doing here, of course.
I’d also suggest that all of your query-related stuff should be inside the first if() clause - if there is no value in $genus, your variable won’t have a value in it when you call the query.
Thanks! In effect the field genere (genre) is a “set” type, it is not a number and it’s is within the same table of other fields.
This is the first time I’m trying to use prepared statements.
If what I said means that I can’t use prepared statements, could you suggest another way to get my aim? Maybe using php function? But I so far I didn’t manage to combine php function and a mysql query…
It doesn’t mean that you can’t use prepared statements, but if the genre is not a number, you can’t use i to indicate the field type in your bind_param(), you’ll need to use an s I think. (I don’t use mysqli, I prefer PDO, so I’m never quite sure of the syntax)
If you’re saying what I think you’re saying, that may be all the more reason that it should be a numeric value, and the string version of it stored in a separate table. Reading again I’m not sure I’m correct though.
The posted code contains a php syntax error in the while … line.
Do you have php’s error_reporting set to E_ALL and display_errrors set to ON, in the php.ini on your system, so that php will help you by reporting and displaying all the errors it detects?
yes: Parse error : syntax error, unexpected ‘$row’ (T_VARIABLE), expecting ‘(’ in /path/myfile.php on line 160 (that is the “while” line, as you said)
after correcting it, new error
**Notice** : Undefined variable: mysqli in **/path/films-prova.php** on line **155**
**Fatal error** : Uncaught Error: Call to a member function prepare() on null in /pat/films-prova.php:155 Stack trace: #0 {main} thrown in **/path/films-prova.php** on line **155**
The line 155 is
$stmt = $mysqli->prepare(“SELECT * FROM films__olon WHERE genere = ?”);
and normally this is what would create your $mysqli object. You might have called it something else, or there might be a problem with it. Hard to say without seeing the code. Or did you mean that the database and table have been created? If you meant that, you need to connect to your database in the code before trying to access it.
yes, you’re right. Now I solved with the correct variable name ($db and not $mysqli).
Now I get this message:
`no rows`.
This query is not to create, but to connect to an exisiting database.
EDIT
I suppose that that message (no rows) is because, as you said above, droopsnoot, I didn’t get the values from a separate table, isn’t it?
If so: I will create it, but how I can declare this new table?
I have added something above (after EDIT), maybe an answer to this your question.
The php file include an inc (header) file in another folder (with generic parametres) + an inc file in the same folder (with specific parametres). And within the file I declare some php variables as well.
No, it’s not. If your table includes any rows where that column matches the value you’re searching for, you should get them. I was just saying that for a proper database design, you should hold these genre names in a separate table and refer to them by their unique id.
If you do want to put them in a separate table, then all you would need is something like
table: genres
id: not null auto-increment
genre_name: varchar(whatever-length)
The id is what you store in your other tables when you need a genre, and you’d use a JOIN to retrieve it when you need to display the name. So instead of your films__olon table containing “fantasy” for example, it would contain “1” and your genres table would have a row with id=1 and genre_name=“fantasy”.
in the «table called films__olon » there is «a field named genere » (correct), however it is not «an integer», but a set (with these values: ‘biografici’,‘drammatici’,‘storici’,‘giovanile’,‘fantasy’,‘fantascienza’, ‘animazione’,‘comici’,‘grottesco’,‘western’,‘giallo’,‘intrigo’,‘avventura’,‘spionaggio’,‘religioso’,‘altro’)
I thought (sorry for my small knowledge) that the instructions:
When you say a “set”, what exactly do you mean? Is it just a string, which will be one (and only one) of the values you listed?
If so, then what you are doing should retrieve all the appropriate rows, and it is strange that it does not. What you need to do next is check the value of $genus to see that it is what you expect it to be.
Have you fixed the problem in this line that I mentioned before?
$stmt->bind_param("i", $genus);
You said that $genus and the column you are comparing it to is not an integer, but your bind_param suggests that it is. As I don’t use mysqli I am not sure whether this will make any difference - if it did not, I imagine the parameter would not be required.