Fetching values from mySQL into a multi-dimensional array

Question

I have an array of 'search tags' submitted by my users, such as apples, oranges, anger, love etc. Each set of these tags are put into an array. The array represents a set of tags submitted by a unique user.

I want to take these tags, submit them into the database, and fetch the corresponding books in the same row as these tags. The books must be in the same kind of array structure as the tags so that they can be assigned back to each user in the html script.

Example: tagsarray[0][1] = 'love' , booksarray[0][1] = 'the book corresponding to 'love''

I had an idea of how I could make this work, using loops and push arrays. I wrote this script below, but it simply isn't working.

<?php


$tags = array(array("apples","oranges", "pears"), 
              array("sun", "blood"), 
              array("anger", "love"));

$con = mysql_connect("Stories.mysql.anyhost.com", "user", "password");

mysql_select_db("Stories");

foreach ($tags as $tag) {

    $one = array();
    $two = array();

    for ($i = 0; $i < count($tag); $i++) {

        $tag_value = $tag[$i];
        $selectbooks = "SELECT * FROM Journeys 
                        WHERE book_tags = '$tag_value'";

        $query = mysql_query($selectbooks);

        while ($row = mysql_fetch_assoc($query)) {
            $one[] = $row['books'];
        }

        $two[] = $one;

    }
}

echo json_encode($two); 
// should echo out [ [books1, books2], [books3, books4], [books5, books6] ] - but instead it returns [[]] 

mysql_close($con);
?>

It would be great if someone could pinpoint the fault with my script and/or suggest a better alternative.

you use `$figure_value` in your query, but never define it. maybe it should be `$tag_value`? — Marc B, Aug 11 '15 at 16:34
Have a look at this: http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display . You will be able to tell us what does *"simply not working"* mean. — , Aug 11 '15 at 16:35
@MarcB and caCtus, Those were just errors when I was typing out my script in this question. I have corrected them. — blurgoon, Aug 11 '15 at 16:37
`count($figure)` is still there. You are counting something that doesn't exist (unless it's a c/c mistake). — , Aug 11 '15 at 16:38
and where's $figure defined? you count that, but never define it, so it's not an array, and would return a count of 0 — Marc B, Aug 11 '15 at 16:39
Corrected them. Sorry I was initially going to use 'figures' to explain the script but I figured 'tags' was easier. — blurgoon, Aug 11 '15 at 16:40
Use [mysql_error()](http://php.net/manual/en/function.mysql-error.php) to check if your queries are executed or not. (Especially the `$selectbooks` one.) — , Aug 11 '15 at 16:45
By the way, [**don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — , Aug 11 '15 at 16:45
did you check you code step by step ? (part by part) for example , echo variables in every loop and checking their values , or check mysql error — Cyrus Raoufi, Aug 11 '15 at 17:11
I added the error statements, (! $php) check statements and switched to mysqli. thanks guys. but I'm still facing the problem. any suggestions as to what's wrong with my code? — blurgoon, Aug 12 '15 at 01:38

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

As you are obviously learning, you would be better to learn the mysqli_ or PDO extension as the mysqli_ one you are using here will not exist in PHP7.

See this for some great reasons and assistance picking between mysqli and PDO

You are doing absolutely no error checking in your code, you should check every mysql access function to make sure nothiing went wrong.

<?php
    // force some error reporting in case you have error reporting turned off.
    error_reporting(E_ALL); 
    ini_set('display_errors', 1);

    $tagsets = array(array("apples","oranges", "pears"), 
                     array("sun", "blood"), 
                     array("anger", "love"));

    $con = mysql_connect("Stories.mysql.anyhost.com", "user", "password");
    if (!$con) {
        die('Could not connect: ' . mysql_error());
    }

    if (! mysql_select_db("Stories", $con) ) {
        die ('Can\'t select the Sories database : ' . mysql_error());
    }


    $toJson = array();  

    foreach ($tagsets as $tagset) {

        $one = array();

        foreach ( $tagset as $idx => $tag ) {

            $sql = "SELECT * FROM Journeys WHERE book_tags = '$tag'";

            $result = mysql_query($sql);
            if ( ! $result ) {
                echo mysql_error();
                exit;
            }

            while ( $row = mysql_fetch_assoc($result, $con) ) {
                $one[] = $row['books'];
            }

            $toJson[] = $one;
        }
    }
    echo json_encode($toJson); 
    mysql_close($con);
?>

I'm still getting this result echoed : [[],[],["Great Expectations"]] — blurgoon, Aug 12 '15 at 01:29
That is what you might get if you only have books that match the `array("anger", "love")` search criteria. Are you sure you database is populated with books that match these tagsets? — RiggsFolly, Aug 12 '15 at 08:01

Fetching values from mySQL into a multi-dimensional array

1 Answers1