Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    23
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Select..From...Where

    Hi,

    I'm trying to do a SELECT command to retrieve two columns of data from my MySQL database in php. I want it to search one particular table and show records that have the users username in column one.
    I know I will need a
    Code:
     SELECT...FROM...WHERE
    but I would be grateful for any advice on the finer details.

    Thanks,
    Jake
    Last edited by jake66; 08-26-2011 at 08:37 AM.

  • #2
    Banned
    Join Date
    Apr 2011
    Posts
    656
    Thanks
    14
    Thanked 69 Times in 69 Posts
    Code:
    select col1, col2 from myTable where col1 = 'someUsername'

  • #3
    New Coder
    Join Date
    Jul 2011
    Posts
    23
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks, this is great, but I would like it so that when the user logs in, the 'someusername' is the logged in users username. Is this possible?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    It would be because you'd store the information in a variable and use the variable in place.

    Code:
    select col1, col2 from myTable where col1 = $loggedinuser

  • Users who have thanked guelphdad for this post:

    jake66 (08-26-2011)

  • #5
    New Coder
    Join Date
    Jul 2011
    Posts
    23
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hello,

    Thanks both for your replies. I think I may have a more general issue here because I am still getting a syntax error in this select command, so I will post more of my code to see if that helps:
    Code:
    <?php
    session_start(); 
    if (!isset($_SESSION['myusername']) || empty($_SESSION['myusername'])) { 
     include "logout.php"; 
    }  ?><br />
    Welcome <? $_SESSION[myusername] ?>! Thank you for logging in.
    <p>Your current absences this month:</p>
    <?php
    SELECT date, service, FROM personsick, personabsent, WHERE person_sick = $session['myusername'] OR person_absent = $session['myusername'] ?>
    By reading my code (I know, its very messy!), you have probably realized that I am a complete beginner with next to no experience, so any help with any part of the code would be very much appreciated.

    Jake

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    If those fields, person_sick and person_absent are TEXT fields of any kind (that is, not numbers) then you need apostrophes around the values being tested for.

    Further, in case the values being tested might contain an apostrophe, you need to "sanitize" them.

    And you seem to be trying to JOIN two tables, but you don't show how the are to be joined. Further, you don't show which field the date and service fields are from. And and and... Are you sure you understand your own database?

    Finally, you can't just stick a SQL query into your PHP code like that. You have to make a connection to the database and use mysql_query and and and...

    I don't code in PHP, so I'll simply direct you to
    http://www.w3schools.com/php/php_mysql_intro.asp

    But I will show you the correct form of the SQL you need to use.
    Code:
    $uname = mysql_real_escape_string( $session["myusername"] );
    $sql = "SELECT `date`, service, FROM personsick, personabsent "
         . " WHERE ...you need the join condition between those two tables... "
         . " AND (person_sick = '" . $uname . "' OR person_absent = '" . $uname . "')";
    
    echo "<hr>DEBUG SQL: " . $sql . "<hr>"; // remove when it starts to work
    
    $result = mysql_query( $sql );
    
    ... and much more ... go read the tutorial ...
    Last edited by Old Pedant; 08-25-2011 at 07:43 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jake66 (08-26-2011)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    In your first post, you wrote
    I want it to search one particular table
    but that SQL query you showed is using *TWO* tables. Which is it?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jul 2011
    Posts
    23
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for your reply,
    I only posted part of my code and yes, I have made a connection to the MySQL database before hand and run the query afterwards.
    The tables person_sick and person_absent are independent of each other, so I think I will do them as two separate queries.

    I think that is pretty much my question answered, thanks guys!

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,198
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    You could do the query as a UNION, if you wanted a single set of records to process in PHP.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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