PHP/Mysql: Why only one row returned?

Posted on 2007-07-20
Last Modified: 2012-05-05
I have the following code

myArray = new Array();
$whereStmt = ' ID="'.$ID.'" AND idx="'.addslashes($passed).'"'; // ID=4, idx = 310;
$selectSql = 'SELECT * FROM myTable WHERE '.$whereStmt ;
$Result    = $MyDb->f_ExecuteSql($selectSql);
$Resultset = $MyDb->f_GetRecord($Result);

$ResultRowNr    = $MyDb->f_GetSelectedRows($Result);


echo "\n// sql:".$selectSql."\n"; // shows // sql:SELECT * FROM myTable WHERE  ID="4" AND idx="310"

while ($Resultset = $MyDb->f_GetRecord($Result)) {
  myArray[<? echo $counter; ?>] = '<? echo $Resultset['title']; ?>';

If I in a separate program I have

$whereStmt = ' ID = "'.&ID.'"; // id = 4

$selectSql = 'SELECT * FROM myTable WHERE '.$whereStmt ;

$Result    = $MyDb->f_ExecuteSql($selectSql);
$recordcount = $MyDb->f_GetSelectedRows();
if ($recordcount==0) {
  echo "After update/insert, I cannot find a record where ".$whereStmt;

echo "<br>Looked up the records after insert/update: ID: ".$ID;

while($row = mysql_fetch_array($Result)) {
  echo "<br>title:".$row['title']." idx: ".$row['idx'];

title:Test310Updated idx: 310

title:Test310Extra_updated idx: 310

In the first prgrams I only get to see the second title. E.g. the result is

  myArray[0] = 'Test310Extra_updated';

and not

  myArray[0] = 'Test310Extra';
  myArray[1] = 'Test310Extra_updated';

So it skips the first.

$ResultRowNr    = $MyDb->f_GetSelectedRows($Result);

have something to do with it? - I do not use it

Also what is the better method? resultSet or fetch?

Question by:Michel Plungjan
    LVL 11

    Accepted Solution

    In the first program, you call f_GetRecord() a first time just after f_ExecuteSql(), which advances to the second row before you start the while loop, remove that line.

    LVL 11

    Assisted Solution

    I think you should use $MyDb->f_getRecord() as you are using a class for database connection, it will make your code more readable and avoid the mix between object-oriented and function-oriented code, but both would be OK, it is just a question of estheticism.
    LVL 3

    Assisted Solution

    i don't know what kind of object  $MyDb is but the f_GetRecord() method might be returning only one row.
    i asume f_ExecuteSql() returns the result resource so you can change your while block into:

    while ($Resultset = mysql_fetch_assoc($Result)) {
    //do stuff

    i would also recommend you change the name of $Resultset as it implies it contains the entire result but it will only contain one row at a time.
    LVL 75

    Author Comment

    by:Michel Plungjan

    I in the meantime changed the code to use
    while($row = mysql_fetch_array($Result)) {

    and that solved it.

    Too many ways to skin a cat in php I think...

    the code has been copied and recopied by the maker a few times and some extra stuff was never removed

    Thanks for helping



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to dynamically set the form action using jQuery.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now