Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0

    MySql, Php & Prep Stmt


    Php Folks,

    Q1. Using PREP STMTs, if I wanted to display on page the LAST row then how would I do it ?

    Example:
    ID|Username|Gender|Birth-Place|Current Residence|Email
    -------------------------------------------
    1|Mr_U|Male|Toronto|Canada| NULL |
    2|Mrs_UI|Female|Seattle|Usa| NULL |
    3|Son_UI|Male|Sydney|Australia|a@a.com|
    4|Daughter_UI|Frankfert|Deutchland|NULL|

    Say, I don't know how may records exist and I want the 4th one (in the above example) pulled since it's the final row. How would I do it ?

    Actually, I'd appreciate a typical code sample where one is mysqli and the other is pdo. The latter should be handy for my pdo tutorials.

    Q2. Looking at the above example in Q1, say you only want the "Birth Places" pulled providing the row has the "Email" column not as NULL (data exists). How would you do it ? A mysli with PREP STMT sample I need now but a pdo sample would be fine for out pdo journey in the next few days.

    Attempt 1

    $email ="";
    $query = "SELECT birthplace FROM users WHERE email != ?";
    $stmt = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($stmt,'s',$email);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_bind_result($stmt,$birth_place);
    mysqli_stmt_fetch($stmt);


    Attempt 2

    $query = "SELECT birthplace FROM users WHERE email = ?";
    $stmt = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($stmt,'s',$email);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_bind_result($stmt,$birth_place);
    mysqli_stmt_fetch($stmt);

    if ($email == "")
    {
    echo "No records found!";
    }


    Can I replace the above to:

    if ($email == "NULL")
    {
    echo "No records found!";
    }



    EDIT

    On Attempt 1, I have made some adjustments (see bold parts).
    Now, I have another attempt. Do you think it is safe even if it works ?


    Attempt 3

    $email ="NULL";
    $query = "SELECT birthplace FROM users WHERE email = ?";
    $stmt = mysqli_prepare($conn, $query);
    mysqli_stmt_bind_param($stmt,'s',$email);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_bind_result($stmt,$birth_place);
    mysqli_stmt_fetch($stmt);

    I reckon php does not recognise "NULL".
    I haven't experimented with any of these 3 attempts yet. But should do so. Did not want to be wasting time fiddling with bad coding. Hence, got you experts to give your opinions first.

    Anyway, since I've given 3 sample codes, how-about you guys give 1 to show your style of coding ?
    Last edited by UniqueIdeaMan; March 22nd, 2018 at 05:30 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,418
    Rep Power
    631
    Originally Posted by UniqueIdeaMan
    Php Folks,

    Q1. Using PREP STMTs, if I wanted to display on page the LAST row then how would I do it ?

    You might try reading the documentation. It is pretty obvious from that how.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,919
    Rep Power
    4554
    Isn't the LAST row just the FIRST row if you switch the ordering? Hmm....
    -- Cigars, whiskey and wild, wild women. --
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Caro, Michigan
    Posts
    14,919
    Rep Power
    4554
    The first attempt should work, from a query perspective. Why are you binding parameters in a query with no parameters, though? I mean, I know the reason why is because you have no idea what you're doing and just copy & paste code, but I'm just making sure.

    I think you can also do "email NOT NULL" as a valid query, but it's been a while.

    This is assuming the values are actually NULL in the table and not "NULL" or an empty string.

    Why aren't you running this **** via the MySQL command line to confirm the query works before you **** it up with PHP?
    -- Cigars, whiskey and wild, wild women. --
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,418
    Rep Power
    631
    res ipsa loquitur
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    62
    Rep Power
    72
    $query = "SELECT birthplace FROM users WHERE email != NULL";
    I think you can also do "email NOT NULL" as a valid query, but it's been a while.
    I think you both need to RTFM regarding the use of null values in queries. Whe checking for null values the syntax is "... IS NULL" or "... IS NOT NULL"

    https://dev.mysql.com/doc/refman/5.7...with-null.html
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    Originally Posted by Barand
    I think you both need to RTFM regarding the use of null values in queries. Whe checking for null values the syntax is "... IS NULL" or "... IS NOT NULL"

    https://dev.mysql.com/doc/refman/5.7...with-null.html
    Thanks for the RTFM link.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    Originally Posted by gw1500se
    res ipsa loquitur
    Veniam in me.
    Ego non intellego quid dicere Mr Goosey!
    Cum causas!
    Help Vampire, hey ?
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    Originally Posted by Sepodati
    Isn't the LAST row just the FIRST row if you switch the ordering? Hmm....
    Mmm. Why didn't I think of ASC & DESC ? So now, all I have to do is just bind a single row.
    Thanks for the hint! You're hints are starting to be very effective & useful to me. I like it. Keep it up!
    You are starting to become my favourite sarcastic programming buddy. Atleast I'm learning or remembering things from your front stabbings.

    EDIT: Look what I found:
    https://stackoverflow.com/questions/...t-row-in-mysql
    Last edited by UniqueIdeaMan; March 22nd, 2018 at 05:41 AM.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    Originally Posted by gw1500se
    You might try reading the documentation. It is pretty obvious from that how.
    I'd rather be the HelpVampire and have you coming to my rescue by giving me sample codes so I can copy & paste. One thing good will come-out from all this is that, I merely do not copy & paste. I experiment changing here and there and learn a thing or 2.

    Comments on this post

    • Sepodati disagrees : You're a piece of ****.
    Last edited by UniqueIdeaMan; March 22nd, 2018 at 05:31 AM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    I have edited my original post. Do check now.

    Cheers Folks!
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    Dear Friends,

    This is working and I am happy.
    Thanks for all your helps. But still, you may still read my EDITED original post and make any comments you deem will be more helpful.

    PHP Code:
    $query "SELECT id,date_and_time,recipient_username,sender_username,notice FROM notices WHERE recipient_username = ? AND sender_username = ? ORDER BY id DESC LIMIT ?"
        
    $stmt mysqli_prepare($conn$query);    
        
    mysqli_stmt_bind_param($stmt,'ssi',$recipient_username,$sender_username,$result_limit);      
        
    mysqli_stmt_execute($stmt);  
        
    $result mysqli_stmt_bind_result($stmt,$id,$date_and_time,$recipient_username,$sender_username,$notice);
        
    mysqli_stmt_fetch($stmt); 
    EDIT
    I have found my answer to Q1 via Sepodati's hint and this link:
    https://stackoverflow.com/questions/...t-row-in-mysql

    Now, I need answers to my Q2.

    Cheers!
    Last edited by UniqueIdeaMan; March 22nd, 2018 at 05:56 AM.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2017
    Posts
    661
    Rep Power
    0
    Folks,

    I now have a little complicated experiment to go through and I appreciate any help you can offer.
    Consider this tbl:


    ID|Username|Url
    -------------------------------------------
    1|Sepodati|301 Moved
    2|UI Man|http://yahoo.com
    3|gw1500se|[/url]http://mamma.com[/url]
    4|Sepodati|http://dogpile.com
    5|Baradd|http://dogpile.com
    6|Baradd|301 Moved Permanently

    Now, I need the last submitted rows of all different Usernames pulled by php via mysql. How would you do it ?
    For your convenience, I have highlighted which rows should show up.
    Note that the Username "Sepodati" has 2 rows and I only want the last row from that Username to be pulled-up. Same goes for "Baradd".
    And note that, the Usernames UI Man & Gw1500se only have one rows and so those one rows should be counted as last rows for these 2 Usernames and get pulled.
    And so, I want results shown like this:


    ID|Username|Url
    -------------------------------------------
    2|UI Man|http://yahoo.com
    3|gw1500se|301 Moved Permanently
    4|Sepodati|http://dogpile.com
    6|Baradd|

    I think you know what I mean.I'd still appreciate if you give the mysql query code. From it, I'll build the php PREPT STMT code.
    Even would appreciate if you give hints to what the mysql query should look like. Like Sepodati usually gives.
    But, if Mysql is not capable of doing what I intend and php has to do it then do say which php functions I should be looking into.

    This is a bit complicated is not it ? Have you ever experimented with the likes of this ?
    And don't forget to address my Q2 as much as you can (if you have not already done so) from my original post.

    Cheers!
    Last edited by UniqueIdeaMan; March 22nd, 2018 at 06:24 AM.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    62
    Rep Power
    72
    SQL is quite capable of doing it. You would use a table subquery to find the max id value for each user then use a join to find the records in your table matching those users/ids
    Code:
    mysql> select * from uniqueidiot;
    +----+----------+-----------------------+
    | id | username | url                   |
    +----+----------+-----------------------+
    |  1 | Sepodati | 301 Moved             |
    |  2 | UI Man   | http://yahoo.com      |
    |  3 | gw1500se | mamma.com             |
    |  4 | Sepodati | http://dogpile.com    |
    |  5 | Baradd   | http://dogpile.com    |
    |  6 | Baradd   | 301 Moved Permanently |
    +----+----------+-----------------------+
    6 rows in set (0.00 sec)
    
    mysql> SELECT id
        ->      , username
        ->      , url
        -> FROM uniqueidiot
        ->      JOIN (
        ->         SELECT username
        ->              , MAX(id) as id
        ->         FROM uniqueidiot
        ->         GROUP BY username
        ->      ) mx USING (username, id);
    +----+----------+-----------------------+
    | id | username | url                   |
    +----+----------+-----------------------+
    |  2 | UI Man   | http://yahoo.com      |
    |  3 | gw1500se | mamma.com             |
    |  4 | Sepodati | http://dogpile.com    |
    |  6 | Baradd   | 301 Moved Permanently |
    +----+----------+-----------------------+
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,653
    Rep Power
    1822
    Barand has addressed this already, via the id column, but I'd add to the mix the following question: "what defines a row as being 'last'?"
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo