SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    nj
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using WHERE function

    im writing a simple piece of code to search through a table and pick out only a certain row:
    here is the query:
    Code:
    $query=mysql_query("SELECT * FROM users WHERE index=5");
    
    $bandname=mysql_result($query,"bandname");
    im thinking this should only find the row with the index = 5 and then take the bandname column and save it to the variable $bandname. but instead i get this error:

    Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/commot/public_html/bb/infopage.php on line 24

    i know theres an easy fix, but i cant figure it out
    http://www.garagepages.com - The Garage Sale Search Engine. Locate a garage sale nearest you

  2. #2
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From the PHP manual:
    mixed mysql_result ( resource result, int row [, mixed field] )

    mysql_result() returns the contents of one cell from a MySQL result set. The field argument can be the field's offset, or the field's name, or the field's table dot field name (tablename.fieldname). If the column name has been aliased ('select foo as bar from...'), use the alias instead of the column name.

    When working on large result sets, you should consider using one of the functions that fetch an entire row (specified below). As these functions return the contents of multiple cells in one function call, they're MUCH quicker than mysql_result(). Also, note that specifying a numeric offset for the field argument is much quicker than specifying a fieldname or tablename.fieldname argument.
    You have the wrong type/number of parameters in your mysql_result() call - you need to specify the row parameter.

    Also, have you called mysql_connect() and mysql_select_db() and verifed that your connection is valid?

    As the manual says, it's preferable to use one of the mysql_fetch_xxx() functions for speed and ease of use.

    There are examples on the website: http://www.php.net

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    nj
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry man, im still not understanding it. i checked out php.net and im lost.

    yes, ive called connect and select_db, everything is good.

    basically i want to search in the database for a row that has an index of 5. once i get that row, i want to take all the fields from that row and save them as variables. i just dont get why i cant do this, when i can do it so easily in asp
    http://www.garagepages.com - The Garage Sale Search Engine. Locate a garage sale nearest you

  4. #4
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Once you have called mysql_connect() and mysql_select_db() with the relevant parameters and obtained a valid connection, you can execute the SQL query to select a given row:

    $result = mysql_query('SELECT * FROM users WHERE index = 5');

    In this case if index is unique it should return only 1 row, but other queries like surname = 'Jones' could potentially return lots. The easiest way to get the data for that row is to fetch it into an array:

    $row = mysql_fetch_array($result);

    $row is now an array of columns for the first row of the results. You can reference its elements numerically (in the order you SELECTed them) or (more usefully) by column name:

    $bandname = $row['bandname'];

    Hope this helps.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    nj
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks djones, i think im getting somewhere, but the error still hasnt gone away. heres what i have:

    Code:
    mysql_connect(localhost,$dbuser,$dbpassword) or die("cannot connect");
    @mysql_select_db($database) or die( "Unable to select database");
    
    
    $query=mysql_query("SELECT * FROM users WHERE index=5");
    	
    	
    	$row = mysql_fetch_array($query);
    	$bbid=$row['fname'];
    	echo $$bbid;

    the error shows: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/commot/public_html/bb/infopage.php on line 20

    line 20 is the $query line
    http://www.garagepages.com - The Garage Sale Search Engine. Locate a garage sale nearest you

  6. #6
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, a couple of things... if you code is exactly like you've posted it, there's a couple of changes I would recommend you make. You should store the resource which is returned by mysql_connect() in a variable. Maybe you should put quotes around localhost in the connect call:

    $conn = mysql_connect('localhost', $dbuser, $dbpassword) or die("cannot connect");

    I'm not convinced that you have got a valid database connection... try putting the following code in after the connect call:

    var_dump($conn);

    This should give you something like:

    resource(x) of type (mysql link)

    If it gives bool(false) instead then your connection is not valid.

    Be careful when using the error suppression operator (@) until you've got everything working, because sometimes the error message can give you further insight as to what's wrong!

    And, finally, in the echo at the bottom of the script:

    echo $$bbid;

    should be:

    echo $bbid; (single $ sign)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try running the query in something other than php so you can see if mysql actually is generating an error message

    "index" sounds like a reserved word so maybe it's barfing on that
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    nj
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i changed it around a bit and heres the new code:
    Code:
    mysql_connect(localhost,$dbuser,$dbpassword) or die("cannot connect");
    @mysql_select_db($database) or die( "Unable to select database");
    $typebm="m";
    
    
    $query="SELECT * FROM users WHERE typebm = $typebm";
    $result=mysql_query($query);
    	
    	
    	$row = mysql_fetch_array($result);
    	$bbid=$row[0];
    	echo $$bbid;
    	echo "---";
    
    
    
    //CLOSE CONNECTION
    mysql_close();
    its still giving me an error in the $row = mysql_fetch part.

    funny thing is i ran the $query in phpmyadmin and it selected a certain row. so it works. whats going on????
    http://www.garagepages.com - The Garage Sale Search Engine. Locate a garage sale nearest you

  9. #9
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are getting the same sort of error:
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource...

    then the query is failing. As I stated in my previous post... I'm not convinced that your connection is valid. Or you could be attempting to access a database or table which you don't have permission to.

    Please check that the mysql_connect() and mysql_select_db() calls are successful and if so, try to get any query working, even a 'SELECT COUNT(*) FROM...'.

  10. #10
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $query="SELECT * FROM users WHERE typebm = $typebm";

    Unless typebm is numeric you should enclose it in quotes (as for all varchars):

    $query="SELECT * FROM users WHERE typebm = '$typebm'";

    otherwise your query would read:

    SELECT * FROM users WHERE typebm = m

    I don't think this is the current problem, because you'd be more likely to get an error like:

    ERROR 1054: Unknown column 'm' in 'where clause'

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Location
    nj
    Posts
    62
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay finally got it to work. but now i have another problem. lets say more than one result is found. its only gona take the last result found in the table. so i figure that i can use a while loop to go from 0 to the number of results found and then output each one:
    Code:
    $query=mysql_query("SELECT * FROM users WHERE typebm='$typebm'");
    //GETS ROW INFORMATION
    $row = mysql_fetch_array($query);
    
    //GETS NUMBER OF ROWS
    $numrows=mysql_numrows($query);
    echo "NUMBER OF MATCHES IN DATABASE:".$numrows."<br>";
    
    
    $i=0;
    while($i<$numrows){
    
    echo $i . ")  " . $row['bbid'][$i]." is a result ";
    
    $i++;
    }
    does this seem right?
    http://www.garagepages.com - The Garage Sale Search Engine. Locate a garage sale nearest you

  12. #12
    SitePoint Addict
    Join Date
    Feb 2004
    Location
    Staffordshire, UK & Florida, USA
    Posts
    314
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nearly. It would actually fetch just the first row of the results if you call mysql_fetch_array() once. It returns a row at a time from the results - you can use a for loop and counter if you need to - or if you just want all the returned rows you can just keep fetching them a row at a time until there are no more rows returned by your query. The mysql_fetch_array() function will return the next row and advance automatically and return FALSE when there are no more rows. So it's common to see something like this:

    PHP Code:
    while ($row mysql_fetch_array($results))
    {
        echo 
    $row['bbid'];



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
  •