How to update mysql_result(mysql_query("SELECT COUNT(*) FROM `table`for php 7.4

[kuau]

I am updating an old site written in php 5.4 to php 7.4 and I am having trouble with this line...


$upcoming = mysql_result(mysql_query("SELECT COUNT(*) FROM `event` WHERE `approved`=1 AND (`startdate` >= '".$today."' OR `oge` > 0) "),0);

I tried this:


if ($result4 = mysqli_query($connection, "SELECT COUNT(*) FROM `event` WHERE `approved`=1 AND (`startdate` >= '".$today."' OR `oge` > 0) ")) { $upcoming = mysqli_num_rows($result4); } but it gave the count as 1 instead of 422.

Not sure what I am doing wrong. Please can one of you php pros shed some light? Thanks.

[james438]

I guess I would need to see what the data is that you are working with, however I do see one error.

Try changing:


$result4 = mysqli_query

to:


$result4 == mysqli_query

[kuau]

Dear James: Thank you. Yes, that was definitely a mistake but, when I fixed it, then I got no result. The COUNT is for the number of upcoming events on an event calendar. You can see what I am trying to fix under the small calendar in the top center of this page: https://www.calendarmaui.com/ Probably I don't even need an IF clause. I'm simply trying to count the number of events in a table that are not expired. There must be something wrong with the COUNT phrase. Has that been deprecated in 7.4? Thanks for helping me. :)

[james438]

Count has not been deprecated, but I noticed after updating my website to PHP 7.4 that some MySQL functions operated somewhat differently.

For now I would get rid of the IF statement until we can figure out the solution and then add it back if you still need it.

What is: SELECT COUNT(*)? Shouldn't it be SELECT * instead?

I would write your query something like this:


$get_list="SELECT * FROM event WHERE approved=1 AND (startdate >= $today OR oge > 0)";
$result4 = mysqli_query($connection, $get_list);
$upcoming = mysqli_num_rows($result4);
echo "number of results found: $upcoming";

[kuau]

Dear James: I just came here to tell you I figured it out by finding an example online here https://www.w3schools.com/Php/func_mysqli_num_rows.asp and then saw your solution which is basically the same as what I changed it to... in the example, they did not use ==, perhaps because it is just checking if there was a valid result...


$sql = "SELECT * FROM `event` WHERE `approved`=1 AND (`startdate` >= '".$today."' OR `oge` > 0)" ;
if ($result=mysqli_query($connection,$sql)){ $upcoming = mysqli_num_rows($result); mysqli_free_result($result); } mysqli_close($connection);

The original code was kind of a shortcut way of coding. It makes more sense to me the way you did it. Anyway, thanks very much. It seems the COUNT function may have been replaced with mysqli_num_rows. Anyway, it works perfectly now. Thanks again! aa :)

[james438]

Just a quick note on IF statements with php.


<?php
$test='0';
if ($test=='1') $test="u";
echo"$test";
?>

The above uses "==" and produces "0".


<?php
$test='0';
if ($test='1') $test="u";
echo"$test";
?>

This second code snippet one "=" and produces "u". This is because by using one = you are setting the value of $test to '1'. I could use anything and it would be true. I could use $test='346' or $test='James' and it would be true.

In the first example, by using two == you create a conditional statement which is either true or false.

[kuau]

Thanks for explaining. So the second case is more like if setting $test to 1 is true, then $test="u"? If the second example is not a conditional statement, why is the 'if' used? It seems kind of pointless if you can just say $test="u".

[kuau]

Thanks, Zinavo, but that code does not use mysqli. I am trying to upgrade to code that will work in 7.4 mysql_query has been deprecated. Sounds as if you may need to update your code too. :)

[james438]

Thanks for explaining. So the second case is more like if setting $test to 1 is true, then $test="u"? If the second example is not a conditional statement, why is the 'if' used? It seems kind of pointless if you can just say $test="u".

Correct. In the second example where only one = is used if ($test='1') $test="u"; it is a conditional statement, but it is always true. Since it is always true, it is not useful as an if statement.

I wanted to be sure you were aware of that, otherwise it might cause trouble for you later.

[kuau]

Dear James: Thank you so much! You made me realize that my code was actually causing the sql command to run twice. I thought it was checking to see if the command was successful but in fact I was re-executing the command. This resulted in every event being added twice to the database and every new location too. OMG. This had been going on for years and made a huge mess. And it all had to do with that fake conditional with the single =. Now I have to check all my code because I seem to have used that all over the place. Thanks for teaching me what it means. :)

[james438]

I'm glad it helped :)

[kuau]

I seem to have that usage down but now I have broken something else. I did update from php 5.4 to 7.4 before I was ready because certain things were not working and I thought that might be why (not!). But I am past the point of no return so now I have to get the whole site working under 7.4. The following code worked under 5.4 in that it put checkmarks only in the categories chosen by the user. Now when I go to approve (edit) a submitted event, ALL the checkboxes are checked. I finally figured out why and marked the error in bold below so that others who may be updating their code won't make the same mistake I did...

<?php

$sql_check = " SELECT event_id, cat_id FROM `eventcat` WHERE event_id = '$event_id' AND cat_id='$cat_id' ";
$result_check = mysqli_query($connection,$sql) or die("Couldn't execute $sql query. <br> mysqli error: ".mysqli_error($connection));

?>

[james438]

Quick question: Is your table name 'category'? It seems like it might be confusing to have a table named 'category' and a column in that table also called 'category'.

The following line does not appear to do anything:


$sql_check = " SELECT event_id, cat_id FROM `eventcat` WHERE event_id = '$event_id' AND cat_id='$cat_id' ";

[kuau]

I tried changing the table name and it made no difference. So I guess that confirms that you can have a field name that is the same as the table name. I finally figured out the second problem. It had nothing to do with my code, which still works fine in 7.4. It was a brain-dead simple mistake I made doing the upgrade of the connection string. I used the wrong $sql variable so it was actually checking the wrong result set from the wrong table. Duh. How embarrassing. Thanks so much for your help and sorry I wasted your time on the second question. What you taught me on the first question has been invaluable for my understanding of php going forward.. many thanks. I don't program that often so my brain is kinda rusty on the language. :)

Here is another thing I have been wondering about. I often use code like this...

if(isset($_GET['event_id']) && $_GET['event_id'] != '') {
And I suspect that it is redundant, but because it works, I am afraid to change it. Isn't it enough to say
if(isset($_GET['event_id']) ? I figure if it exists, we already know it is not equal to nothing. Just not 100% sure. Do you know? aa :)

[james438]

I'm glad you were able to get things figured out. I felt like I needed to be more involved with the full code to see what was going on. Still, I am glad it is working now.

Regarding your question regarding isset(), it is not redundant, but you are probably fine doing if(isset($_GET['event_id']) instead.

I have had a bit of trouble with this bit of code myself. For example, in the following code snippet when I assign the "value" of '' to $test as in $test='';, $test becomes not Null.


<?php
$result="Null";
$test='';
if (isset($test)) {$result="Not Null";}
echo"test is $result.";
?>

In this case it might be better to look at the first type comparisons (https://www.php.net/manual/en/types.comparisons.php) chart on php.net.

[kuau]

My code was distracting from the source of the error. It was in the mysqji update line and that was all. The rest had nothing to do with it. I was mistakenly re-running the previous query. It was just a dumb error.

I hadn't seen that chart before.. very helpful. Once again you have taught me something I was never quite sure about. I'll keep using
if(isset($_GET['event_id']) && $_GET['event_id'] != '') { now that I see it is not redundant. Thanks! :)

[kuau]

You're the only one who seems to answer questions, so I'm hoping you can please help me with this. I have watched videos about $_SESSION variables and I understand their purpose but I don't see how they protect files in directories. I am writing a login script and once the person successfully logs in and is allowed access to the database, what is to stop them using the URL to go straight to the script? It seems to me the only thing protecting the file is the fact that they don't know the filename until they login and then they can get there by typing the URL instead of logging in. What am I missing? Thanks, aa :)

[keyboard]

Hey kuau! Hope it's ok I dropped in here.

Just wanted to check a couple of things regarding your project setup; apologies if I missed details above.
1. Are the file paths stored in the database? If not, what is the data that the user gets access to from the database once they are logged in?
2. Are the files different (dynamic) for each user (e.g. user1 goes to /file.txt and sees "Hi user1", while user2 goes to /file.txt and sees "Hi user2") or are they static and unchanging?

In terms of how sessions can be used to protect files, there are several potential options (I've included only a couple below!). The correct approach depends heavily on requirements such as project structure, security requirements, etc.

1. Don't expose the file directly. When the user tries to access a resource that you want to protect, have a PHP script instead. In the script, check if the user is authenticated (e.g. by checking the session). If they are, have the PHP script return the contents of the file. If they aren't, redirect them to login. Pretty common way of doing this is having a PHP endpoint such as getfile.php?fileID=somefileid and returning different files based on the fileID. It's worth noting that this can lead to some pretty nasty security issues if implemented incorrectly, so definitely do a bit of reading if that's how you want to approach it!

2. If the file contents are unique to the user, you can generate a long, unpredictable file location for each person. That way, even if a user does save the url of the file, it will only be relevant to them. You can see this pretty commonly on file sharing websites, where they'll generate a long filename to protect resources (google drive's file share does this for example). This approach can also have issues, as urls tend to be stored in a lot of places, like browser caches, proxies, server logs, etc. Again, it depends on the project requirements, security, etc.

3. If the contents are unique to each user, you can generate a unique and unpredictable filename for each user. That way, even if they do save the url, they're only accessing their data.


As mentioned before, there are definitely other approaches to this issue, but I hope this gives you some ideas!



--edit
Also, just wanted to mention that we usually recommend starting a new thread for a question that isn't directly related to the existing one.

[kuau]

Dear keyboard: Thank you so much for your reply. I did post a separate thread under "Searching for such a script" on March 18th but got zero replies. That's why I'm stuck trying to write my own login script. I am trying to allow people who submit events to a community event calendar to edit their own events submitted under their email address. So once they successfully login, I present a list of their events as links they can click on to access an edit form. So yes, the results are different for each submitter, but what's to stop someone from changing the email address in the query string to access someone else's events? Is it a bad idea to allow people to edit directly? I really don't have time to maintain this calendar but the people for whom I wrote it have moved away and asked me to take over. I'm hoping it will be less work if people can edit their own events. Perhaps I should allow only certain people by invitation only. Not sure how much danger there is to the database. Thanks. :)

[keyboard]

Dear keyboard: Thank you so much for your reply. I did post a separate thread under "Searching for such a script" on March 18th but got zero replies. That's why I'm stuck trying to write my own login script.
No problem! I did see your other post, but unfortunately, I have no experience with Tiny MCE so I didn't feel comfortable responding.



I'm hoping it will be less work if people can edit their own events. Perhaps I should allow only certain people by invitation only. Not sure how much danger there is to the database. Thanks. :)
What you're looking for doesn’t sound too complex. There are two simple options that I can think of:
1. Allow users to register using their email address and a password. During the registration process, send them an email with a verification code to confirm they own the email. When they visit your site, they login using their email and password and the system then allows them to edit the entries associated with their email address.

2. The user visits your calendar and enters their email address. The system emails them a temporary access code, and this code allows them to create/edit events with their email address.

Option one is the more powerful option - it is a full login system, and they don't have to verify their email each time they want to edit their events.
Option two is the simpler option - there is no persistent login system, but they must verify their email each time they want to access their events.
Both options require sending emails. Is your webserver setup to send emails?


I am trying to allow people who submit events to a community event calendar to edit their own events submitted under their email address. So once they successfully login, I present a list of their events as links they can click on to access an edit form.
Ah thanks, that helps a lot to clarify what you’re trying to achieve. I assume the events are already setup and stored in a MySQL database?


So yes, the results are different for each submitter, but what's to stop someone from changing the email address in the query string to access someone else's events? Is it a bad idea to allow people to edit directly?
For your scenario, I’d recommend adding a verification check on the PHP endpoint that is handling the editing of the calendar. The logic flow could be something such as:
1. User visits your site and authenticates (either with email + password or one time code [see below])
2. The server has now confirmed that the user owns the email address <x>. Store the validated email in the user’s session
3. On the post-login page, query the database to select all entries where the email is <x>. Use the email stored in the session, not any input from the user. Generate the list of links and display it to the user
4. Each calendar link points to the same PHP page with a get parameter to specify the calendar item they’re accessing. E.g. /editCalendarEvent.php?id=<y> (where <y> is the ID of the item).
5. The user clicks one of the links and is redirected to /editCalendarEvent.php?id=<y>
6. The script queries the database to get the details of the relevant calendar item. You check to ensure that the value of the email in the user’s session matches the email stored against calendar item <y> in the database. You will need to sanitise all input that the user can impact (to prevent injection attacks). This means santising both the id value <y>, and the user’s email address <x>. You’d check that they have access when they first navigate to the edit page (to ensure they’re viewing the editing page for their event), and again when they submit the edit (to ensure they’re submitting an edit on a calendar item they own).

A sample query for loading a calendar event only for the user who owns it is included below. Just note I haven’t tested it so there may be errors, and it also needs more stuff added to it such as validation / error handling. You’d implement a similar process for updating the value of an existing item in the database.
I would highly recommend using prepared PDO statements, instead of MySQLi, as it has strong benefits including ease-of-use, security, and speed.


<?php

/*
Please note that this example doesn't really account for error handling
Some of the code, such as the PDO connection may throw an error such as PDOException
There's a lot of disagreement about how to handle errors in PHP

It doesn't really matter if you want to put it in a try/catch statement, or if you want to configure PHP to
handle the error gracefully, or if you want to bind an error handler.

All you need to make sure is that you don't display error messages + debugging info to the user.
Log the error / debugging info and give the user a generic error instead.
Intersting post here - https://phpdelusions.net/articles/error_reporting and some more opinions here - https://stackoverflow.com/a/6455041
*/

session_start();

//Update this to your connection parameters. If you're security conscious, you should move these values out of source code and into a configuration file. See https://stackoverflow.com/questions/97984/how-to-secure-database-passwords-in-php
$dbHost = "localhost";
$dbName = "db";
$dbUser = "username";
$dbPass = "password";

try {
$pdo = new \PDO("mysql:host=${dbHost};dbname=${dbName};charset=utf8", $dbUser, $dbPass);
$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
}
catch(\PDOException $e) {
//Don't print exception messages ($e) to users. See my comment at the top of the code for more info.
die("Connection error");
}

//Customise your SQL query as required
$stmt = $pdo->prepare("SELECT * FROM calendarEvents WHERE id=:id AND email=:email");

//Even though using prepared statements protects from SQL Injection, you still need to validate both the ID and email! Don't use without validation!
$stmt->bindParam(':id', $_GET['id']); //The calendar ID parameter from the URL
$stmt->bindParam(':email', $_SESSION['email']); //The user's email (from session)

//Execute the SQL query
$calendarItem = $stmt->fetch();

//That combination of event ID and user email doesn't exist in the database.
//Could be a user trying to access someone else's event, or an event that doesn't exist, etc.
if(!$calendarItem) {
//Ideally you would gracefully handle this issue instead of using die
die("That calendar event couldn't be found.");
}

//$calendarItem now contains the item you want to display.
?>


Please feel free to ask more questions, or for help with specific code.

[kuau]

Dear keyboard: I am so grateful for your detailed responses. I am learning so much. I have never used PDO before so have to watch some videos. This is helping immensely. I have made it to the point of sending the verification email but it is 4:50am and I can't keep my eyes open so must t bed for now. I shall try for more tomorrow. Thanks again. :)

[keyboard]

No problem! Glad to hear you're learning new things :)
If you run into any obstacles, don't hesitate to ask