Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

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

  1. #11
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,345
    Thanks
    99
    Thanked 111 Times in 109 Posts

    Default

    I'm glad it helped
    To choose the lesser of two evils is still to choose evil. My personal site

  2. #12
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    636
    Thanks
    279
    Thanked 15 Times in 15 Posts

    Default

    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...
    Code:
    <?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));
    
      ?>
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	category-check-boxes.jpg 
Views:	32 
Size:	37.2 KB 
ID:	6422  
    Last edited by kuau; 03-06-2021 at 09:05 PM. Reason: corrected a dumb mistake

  3. The Following User Says Thank You to kuau For This Useful Post:

    kija (03-05-2021)

  4. #13
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,345
    Thanks
    99
    Thanked 111 Times in 109 Posts

    Default

    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:

    Code:
     $sql_check = " SELECT event_id, cat_id FROM `eventcat` WHERE event_id = '$event_id' AND cat_id='$cat_id' ";
    To choose the lesser of two evils is still to choose evil. My personal site

  5. The Following User Says Thank You to james438 For This Useful Post:

    kija (03-05-2021)

  6. #14
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    636
    Thanks
    279
    Thanked 15 Times in 15 Posts

    Default

    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...
    Code:
    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
    Code:
    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
    Last edited by kuau; 03-06-2021 at 09:21 PM.

  7. #15
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,345
    Thanks
    99
    Thanked 111 Times in 109 Posts

    Default

    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.

    Code:
    <?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 chart on php.net.
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #16
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    636
    Thanks
    279
    Thanked 15 Times in 15 Posts

    Default

    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
    Code:
    if(isset($_GET['event_id']) && $_GET['event_id'] != '') {
    now that I see it is not redundant. Thanks!

  9. #17
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    636
    Thanks
    279
    Thanked 15 Times in 15 Posts

    Default

    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
    Last edited by kuau; 04-03-2021 at 04:00 PM.

  10. #18
    Join Date
    Mar 2011
    Posts
    2,090
    Thanks
    59
    Thanked 111 Times in 108 Posts
    Blog Entries
    4

    Default

    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.
    Last edited by keyboard; 04-03-2021 at 10:01 PM.

  11. #19
    Join Date
    Sep 2007
    Location
    Maui
    Posts
    636
    Thanks
    279
    Thanked 15 Times in 15 Posts

    Default

    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.

  12. #20
    Join Date
    Mar 2011
    Posts
    2,090
    Thanks
    59
    Thanked 111 Times in 108 Posts
    Blog Entries
    4

    Default

    Quote Originally Posted by kuau View Post
    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.


    Quote Originally Posted by kuau View Post
    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?

    Quote Originally Posted by kuau View Post
    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?

    Quote Originally Posted by kuau View Post
    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 Code:
     <?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.

  13. The Following User Says Thank You to keyboard For This Useful Post:

    kuau (04-05-2021)

Similar Threads

  1. Replies: 7
    Last Post: 02-07-2013, 03:25 PM
  2. Replies: 6
    Last Post: 04-06-2009, 11:27 AM
  3. I want to sort the table "sql:select results query" to appear in sequence of the arra
    By leonidassavvides in forum MySQL and other databases
    Replies: 0
    Last Post: 12-26-2008, 11:09 PM
  4. "select * " vs "select col1, col2 "
    By james438 in forum MySQL and other databases
    Replies: 5
    Last Post: 04-03-2007, 08:40 AM
  5. Select From Table Where Fieldvalue != ""
    By centenial in forum MySQL and other databases
    Replies: 1
    Last Post: 07-03-2006, 06:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •