PHP mysqli_fetch_assoc to store data in an array

Question

I have a list of url's(link) in my database and can echo the data to the page fine but instead of outputting it, I need to store that info(I was thinking an array) into a variable to perform php tasks using the provided links. I have yet to figure out how to do this.

The code has been updated I removed any references to using the soon to be deprecated mysql_* functions and opted for the mysqli version.

Heres my code

$query = "SELECT `Link` FROM `Table1` WHERE `Image` ='' AND `Source`='blah'";

if ($result = mysqli_query($dblink, $query)) {

while ($row = mysqli_fetch_assoc($result)) {  
    $link = $row['Link'];
    // echo ''.$link.'<br>';
        $html = file_get_html($link);
        foreach ($html->find('div.article') as $e) {
            $result = $e->find('img', 0);
            $imgsrc = $result->src . '<br>';
            echo $imgsrc;
        }
    }
}

This code is working through one iteration: It will find the first link stored in the DB, use that $link in the bottom foreach() statement and output the desired result. After the first iteration of the loop, an error occurs stating:

"mysqli_fetch_assoc() expects parameter 1 to be a mysql result"

I think I understand why the problem is occurring - Since the $result is declared outside of the while loop, it is never set again after the first iteration/or changes in some way.

or

I should be using mysqli_free_result() possibly, If that were the case I am not sure where it would go in the code.

Thanks for any help you can offer!

Sidenote: You might want to look into replacing your mysql_* functions as they are deprecated as of PHP 5.5.0. Try using mysqli or pdo instead. — PhearOfRayne
– PhearOfRayne, Commented Dec 19, 2012 at 2:05
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — John Conde
– John Conde, Commented Dec 19, 2012 at 2:06
Thanks for the heads up. It's been over a year since I have done anything with MySQL and Im just getting back into it. I'll check out the documentation asap. Thanks! With that said, I would assume even with out using the mysql_* functions I would still have the problems I am currently having — Twhyler
– Twhyler, Commented Dec 19, 2012 at 2:16

Jasper · Accepted Answer · 2012-12-19 08:15:12Z

1

When you do this:

$result = mysqli_query($dblink, $query);

The functions return a link identifier you store in $result. This identifier we need to pass to fetch functions in order to be able to show it from which result to fetch. It shouldn't be changed until you are done fetching all the results you want.

This goes right the first time:

$row = mysqli_fetch_assoc($result)

But then, in the foreach, you overwrite that variable with other information:

$result = $e->find('img', 0);

As such, when the next iteration comes around, it is no longer a valid result identifier, so MySQL doesn't know what to do with it.

The fix is actually rather simple, you need to change the name of the variable you are using in the foreach:

$result = $e->find('img', 0);
$imgsrc = $result->src . '<br>';

Becomes:

$found= $e->find('img', 0);
$imgsrc = $found->src . '<br>';

And voila, it should work...

answered Dec 19, 2012 at 8:15

Jasper

12k6 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Twhyler Over a year ago

Thanks for the help! Works fine now. I have ran it a few times and now it will stop after about 40 links and wont output any errors. Just stops. I checked the LAMP error log and it is showing an exit signal bus error (10) when the problem happens. Any input on what that could be?

CodeZombie · Accepted Answer · 2012-12-19 02:31:27Z

1

Your snippet is full of potential errors:

1) Not checking if query succeeded

$query_run = mysql_query($query)

You execute a query, but you never check if your query succeeded by verifying if $query_run is an actual resource and not FALSE.

2) Validation of rows returned

Your validation for the number of rows returned by the query is useless:

if (mysql_num_rows($query_run)==NULL) { 
    echo 'No results found.';
}

This is never true, as mysql_num_rows() returns an inte or FALSE, never NULL.

3) Use of variable with potentially invalid value

Using

while ($query_row = mysql_fetch_assoc($query_run)) { ... }

is risky as you never check if $query_run is an actual resource, which is required by mysql_fetch_assoc().

4) Misunderstanding of while loop

The following lines are probably wrong too:

while ($query_row = mysql_fetch_assoc($query_run)) {
    $link = $query_row['Link'];
    // echo ''.$link.'<br>';

}
$html = file_get_html($link);

You iterate over all rows returned by the query. After the while loop exits, $link only contains the value of the last row as single variable cannot contain the values of multiple rows.

Conclusion

I strongly recommend you improve your error checking and improve the overall quality of your code. Also consider using one of the newer extensions like mysqli or PDO, the mysql extension is deprecated.

edited Dec 19, 2012 at 2:31

answered Dec 19, 2012 at 2:14

CodeZombie

5,3753 gold badges32 silver badges37 bronze badges

1 Comment

Twhyler Over a year ago

I updated the code above, if you would be willing to take another look

SeanWM · Accepted Answer · 2012-12-19 02:13:55Z

0

If you want to add all links to an array try this:

$link[] = $query_row['Link'];

Instead of:

$link = $query_row['Link'];

You were close but you weren't using square brackets you were using parentheses as shown here:

$link = $query_row($link);

Also, try taking $query_run out of the if statement. It should look something like this:

$query = "SELECT `Link` FROM `Table1` WHERE `Value1` ='' AND `Source`='blah'";
$query_run = mysql_query($query);
if ($query_run) {
    echo 'Query Success!<br><br>';
    if (mysql_num_rows($query_run) == NULL) {
        echo 'No results found.';
    }
    while ($query_row = mysql_fetch_assoc($query_run)) {
        $link[] = $query_row['Link'];
        // echo ''.$link.'<br>';

    }
    $html = file_get_html($link);
    foreach ($html->find('div.article') as $e) {
        $result = $e->find('img', 0);
        $imgsrc = $result->src . '<br>';
        echo $imgsrc;
    }
}

answered Dec 19, 2012 at 2:13

SeanWM

17.1k10 gold badges54 silver badges85 bronze badges

2 Comments

Twhyler Over a year ago

Thanks for the help!, I updated the code above and took a bit of advice from the different answers.

SeanWM Over a year ago

Glad to help. Make sure to accept the answer that helped out most!

dualed · Accepted Answer · 2012-12-19 02:38:14Z

0

You should revisit the PHP Language Reference.

The foreach loop syntax is

foreach($array as $element)

or

foreach($array as $key=>$value)

But you seem to have other weak points that I fear are not in the scope of Stackoverflow to mend. For example your own code would work quite well by just moving a single } from line 11 down a few lines.

edited Dec 19, 2012 at 2:38

answered Dec 19, 2012 at 2:29

dualed

10.5k1 gold badge28 silver badges30 bronze badges

1 Comment

Twhyler Over a year ago

OK I will take a look at the foreach loop syntax. Thanks for the link!

Collectives™ on Stack Overflow

PHP mysqli_fetch_assoc to store data in an array

4 Answers 4

1 Comment

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related