Asked  10 Months ago    Answers:  5   Viewed   11 times

Ok I have a table with a few fields. One of the fields is username. There are many times where the username is the same, for example:

    • username: bob
    • password: bob
    • report: 1
    • username: bob
    • password: bob
    • report: 2

I did a SQL statement to select * where username='bob'; but when I do the following PHP function, it will only return the last result:

$thisrow = mysql_fetch_row($result);

I need to get every field from every row. How should I go about doing this?

$mainsection="auth"; //The name of the table
$query1="select * from auth where username='$user'"; 
$result = mysql_db_query($dbname, $query1) or die("Failed Query of " . $query1);  //do the query
$thisrow=mysql_fetch_row($result);
echo "Study: " . $thisrow[1] . " - " . $thisrow[5];

Sorry for such a dumb question. I can't seem to get the while loops of more than one field working for the life of me.

 Answers

5

mysql_fetch_row fetches each row one at a time. In order to retrieve multiple rows, you would use a while loop like this:

while ($row = mysql_fetch_row($result))
{
    // code
}
Saturday, August 7, 2021
 
5

I would advise a very simple SQL join. Assuming the site name is sitename in the sites_tbl:

$sql  = "SELECT sheet.sheet_id, sheet.username, site.sitename FROM sheet_tbl S
       JOIN sites_tbl ST ON ST.site_id = S.site_id ";
$stmt = $conn->prepare($sql);
$stmt->execute();
$data = $stmt->fetchAll();


  <?php foreach ($data as $row): ?>
    <tr>
        <td><?=$row['sheet_id']?></td>
        <td><?=$row['username']?></td>
        <td><?=$row['sitename']?></td>
   </tr>

So now you not only have the data from sheet_tbl but also the associated data from sites_tbl that you can now use directly.

Read more about joins here: http://www.w3schools.com/sql/sql_join.asp

Thursday, April 1, 2021
 
2

Probably you are looking for Pagination, this can be done through mysql LIMIT

Something like that

$result = $mysqli->query("SELECT uid, title, description, tblFacilityHrsDateTimes.* FROM tblFacilityHrs LEFT JOIN tblFacilityHrsDateTimes ON tblFacilityHrs.uid = tblFacilityHrsDateTimes.owner_uid ORDER BY tblFacilityHrs.title LIMIT 15, $page ")

Where $page is for offset

Here are few tutorial for creating custom pagination

Pagination of MySQL Query Results

Pagination - what it is and how to do it

Saturday, May 29, 2021
 
ioleo
 
3

Here is an example for using PDO to connect to a DB, to tell it to throw Exceptions instead of php errors (will help with your debugging), and using parameterised statements instead of substituting dynamic values into the query yourself (highly recommended):

// connect to PDO
$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password");

// the following tells PDO we want it to throw Exceptions for every error.
// this is far more useful than the default mode of throwing php errors
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare the statement. the placeholders allow PDO to handle substituting
// the values, which also prevents SQL injection
$stmt = $pdo->prepare("SELECT * FROM product WHERE productTypeId=:productTypeId AND brand=:brand");

// bind the parameters
$stmt->bindValue(":productTypeId", 6);
$stmt->bindValue(":brand", "Slurm");

// initialise an array for the results
$products = array();
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $products[] = $row;
}
Thursday, June 3, 2021
 
rasmusx
 
3

Here's the basic template for this kind of thing, using built-in php functions (assuming old-style mysql, but similar using other database back-ends, or higher-level libraries). In this example, errors are handled by throwing exceptions, but that's just one way to do it.

  1. Connect to the database
  2. Make sure connection was successful
  3. Run the query
  4. Make sure the query didn't fail for some reason (usually a SQL syntax error). If it did fail, find out why and handle that error
  5. Check that the query returned at least one row (zero rows typically is a special case)
  6. Loop over the returned rows, doing whatever it is you need done.

The exception classes would need to be defined (they're the only non-built-in syntax here, but you shouldn't throw plain-vanilla Exceptions).

Example Code:

<?PHP
//try to connect to your database.
$conn = mysql_connect(...);

//handle errors if connection failed.
if (! $conn){
    throw new Db_Connect_Error(..); 
}   

// (try to) run your query.
$resultset = mysql_query('SELECT ...');

//handle errors if query failed.  mysql_error() will give you some handy hints.
if (! $resultset){ 
    // probably a syntax error in your SQL, 
    // but could be some other error
    throw new Db_Query_Exception("DB Error: " . mysql_error()); 
}

//so now we know we have a valid resultset

//zero-length results are usually a a special case    
if (mysql_num_rows($resultset) == 0){   
    //do something sensible, like tell the user no records match, etc....
}else{
    // our query returned at least one result. loop over results and do stuff.
    while($row = mysql_fetch_assoc($resultset)){
        //do something with the contents of $row
    }
}
Thursday, January 13, 2022
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :