For Each Unexpected Result

[MoFish]

Hi,

 

I have a foreach running on the results for SQL query, but only get two results appearing.

 

I am expecting three to be displayed.

 

Can anyone see any major problem with this?

    public function get_all_league()
    {
        $db = new sql();
        $sql = "SELECT * FROM $this->tablename";
        $db->query($sql);
        $data = $db->fetch_array();
        $section_object = new section_object();
    
        if(!empty($data)){
            while($k = $db->fetch_array($db))
            {
                $total[] = $k;
            }
        }
        return $total;
    }

Thanks,

 

MoFish

Edited November 23, 2013 by MoFish

[Ch0cu3r]

 

ut only get two results appearing.

What are you expecting? 

 

 

I have a foreach running on the results for SQL query,

What foreach?

[MoFish]

What are you expecting? 

 

What foreach?

 

Appologies,

 

It is a while statement:

while($k = $db->fetch_array($db))

There are three entries in the database and it is only returning two in the print_r statement.

 

Do I need to maybe add the values of $data into the total[] array I am returning somehow?

 

Thanks

 

MoFish

Edited November 23, 2013 by MoFish

[Ch0cu3r]

This is because you call $db->fetch_array(); before the while loop. Each time you call this it'll return a row from the query.

 

Basically yourare losing a result for checking if the query returned anything. What you should be doing is use mysqli_num_rows (<-- mysqli) or mysql_num_rows (mysql)  to check for this.

Edited November 23, 2013 by Ch0cu3r

[MoFish]

Hi,

 

I updated the code as suggested - but it now returns no values.

 

Can you clarify if i got this correct?

 

Should the fetch_array still be in the while loop as I had previously?

    public function get_all_league()
    {
        $db = new sql();
        $sql = "SELECT * FROM $this->tablename";
        $db->query($sql);
        $data = $db->mysql_num_rows();
        $section_object = new section_object();
    
        if(!empty($data)){
            while($k = $db->fetch_array($db))
            {
                $total[] = $k;
            }
        }
        return $total;
    }

Edited November 23, 2013 by MoFish

[objnoob]

You're looping through each item in the $data array. The number of items in the $data array depends on the fetch mode you're using.

Since you're not explicitly specifying a fetch mode (fetch num, fetch both, fetch assoc, etc) you will have to assume the default fetch mode is being used.

 

The default fetch mode, assuming you're using fetch_array() method of a mysqli_result object, is both. This will represent each column of a mysqli_result row twice; once by column index and once by column name.

Since each column is represented twice, any and all row arrays (fetch_array) fetched will have an even number of items. x columns multiplied by 2 representations will always total an even number of representations (2, 4, 6, . So, expecting 3 is just not possible.

 

Now that we've covered your fetch both mode paradox, there are other inhibitions that I spot in your code.

 

Caliing $db->query($sql); should, if it doesn't, result with a return value that is not being stored. You should design the query() method of your sql class to return a value that represents the query's result!

          $the_returned_result  = $db->query($sql);      // query the database and set the variable $the_returned_result to the returned value of sql::query()

          $data = $the_returned_result->fetch_array(); // fetch from the returned value

[objnoob]

This is because you call $db->fetch_array(); before the while loop. Each time you call this it'll return a row from the query.

 

Basically yourare losing a result for checking if the query returned anything. What you should be doing is use mysqli_num_rows (<-- mysqli) or mysql_num_rows (mysql)  to check for this.

 

No need, since the query has a LIMIT clause.

[MoFish]

    function query($sql_query)
    {
        
        $this->sql_query=$sql_query;
        $this->sql_result=mysql_query($this->sql_query);
        $this->error=mysql_error();
        if (!$this->sql_result)
        {
            echo "<b>" . $sql_query . "</b><br><br>";
            $this->error=mysql_error();
            echo $this->error;
        }
        echo mysql_error();
    }

is my query function objnoob

Edited November 23, 2013 by MoFish

[MoFish]

So let me get this right - I am OK to continue to use the fetch_array() approach I had previously?

 

However I need to amend the query() function to return a value?

[Ch0cu3r]

No need, since the query has a LIMIT clause.

What? Where is the limit here

 $sql = "SELECT * FROM $this->tablename";

How else are you supposed to check if the query returned anything.

[MoFish]

    public function get_all_league()
    {
        $db = new sql();
        $sql = "SELECT * FROM $this->tablename";
        $returned = $db->query($sql);
        $data = $returned->$db->fetch_array();
        $section_object = new section_object();
    
        if(!empty($data)){
            while($k = $db->fetch_array($db))
            {
                $total[] = $k;
            }
        }
        return $total;
    }

Updated code. No Joy

[MoFish]

What? Where is the limit here

 $sql = "SELECT * FROM $this->tablename";

How else are you supposed to check if the query returned anything.

 

There is no LIMIT you are correct with this...

[objnoob]

Yes, it would be wise.  However, you are storing the result in $this->sql_result.

So, you COULD just $data = $db->sql_result->fetch_array();   I don't recommend this because it limits you to 1 sql_result, and you will not be able to use sql::query() again while relying on sql::sql_result.

[MoFish]

My brain is melting

 

I'm now getting no results at all.

Edited November 23, 2013 by MoFish

[Ch0cu3r]

Yes, it would be wise.  However, you are storing the result in $this->sql_result.

So, you COULD just $data = $db->sql_result->fetch_array();   I don't recommend this because it limits you to 1 sql_result, and you will not be able to use sql::query() again while relying on sql::sql_result.

That would be the case if MoFish is using mysqli object.

 

The mysql_*() is procedural code only

[objnoob]

The limit is here: 

 

 $sql = "SELECT * FROM $this->tablename WHERE `staff_id`='".clean_db($id)."' LIMIT 1";

 

This means sql::sql_result would be no more than 1 row ALWAYS!  So, checking the number of rows is moot.   Fetch once.... if the fetch is false on the first fetch, you've got 0 rows, otherwise 1. Second fetch is not necessary, but may require a free result or close cursor to free up the database's result buffer.

[MoFish]

objnoob I've scrolled up my posts and do not see the same code posted as you have quoted in your sql above.

[objnoob]

That would be the case if MoFish is using mysqli object.

 

The mysql_*() is procedural code only

 

Yeah, I see.  He's knee deep without the basics.  I'm sure it's a copy paste snippet here and snippet there collection of code statements in an attempt.

[MoFish]

There should be no LIMIT in the SQL

[Ch0cu3r]

@objnoob are you reading/replying to the right thread?

[MoFish]

Yeah, I see.  He's knee deep without the basics.  I'm sure it's a copy paste snippet here and snippet there collection of code statements in an attempt.

 

Aww, I thought I had done rather well to get the results coming out from the database - I just couldn't figure out why I was missing one. So what exactly am I missing? Have i been lead astray during the cross wires of posts somehow? lol

Edited November 23, 2013 by MoFish

[MoFish]

@objnoob are you reading/replying to the right thread?

 

lol - I'm very confused also...

[objnoob]

objnoob I've scrolled up my posts and do not see the same code posted as you have quoted in your sql above.

 

Yep. There's a number of issues and misunderstandings you're experiencing, so i'll end with a single suggestion... 

 

KISS

[objnoob]

Here's the original...

 

Hi,

 

I have a foreach running on the results for SQL query, but only get two results appearing.

 

I am expecting three to be displayed.

 

Can anyone see any major problem with this?

    public function get_all_league()
    {
        $db = new sql();
        $sql = "SELECT * FROM $this->tablename";
        $db->query($sql);
        $data = $db->fetch_array();
        $section_object = new section_object();
    
        if(!empty($data)){
            while($k = $db->fetch_array($db))
            {
                $total[] = $k;
            }
        }
        return $total;
    }

Thanks,

 

MoFish

[objnoob]

what the heck, lol. i'm not crazy......  this is copy + paste:  

 

Hi,

 

I have a foreach running on the results for SQL query, but only get two results appearing.

 

I am expecting three to be displayed.

 

Can anyone see any major problem with this?

public function get_league($id)
{
$db = new sql();
$sql = "SELECT * FROM $this->tablename WHERE `staff_id`='".clean_db($id)."' LIMIT 1";
$db->query($sql);
$data = $db->fetch_array();
$section_object = new section_object();
foreach($data as $k => $v)
{
if(!is_numeric($k))
$section_object->$k = $v;
}
return $section_object;
}

Thanks,

 

MoFish

• Like This

• Quote
• MultiQuote
•  
•