How to work with data from mysql in php-multidimensional array

Question

I get null values when I run this code

$dataArray = mysql_query ("SELECT * from _$symbol order by date DESC limit 10;");

while ($ArrayData = mysql_fetch_assoc($dataArray)) {
    $dayData [] = $ArrayData;
    }

$todaysdate = $dayData[0]['date'];

$volPercentAVG = $dayData[0]['volume'] / $dayData[0]['_50dayVol'];
    mysql_query ("update _$symbol set volPercentAvg=$volPercentAVG WHERE date=$todaysdate;");

It does not return anything, I am not sure I am approaching the MDarray correctly? I have triple checked the column names.

Anywhere to do with this would be helpfull

Thanks.

well, if `var_dump($ArrayData)` returns nothing, then `SELECT * from _$symbol order by date DESC limit 10;` returns nothing. have you tried the query in phpMyAdmin? — Félix Adriyel Gagnon-Grenier, Oct 06 '14 at 17:58
Use single quotes around query to suppress `$` inplace expansion. Inside double quotes `$symbol` is being substituted with value of `$symbol` variable, which is apparently empty. — Aleksei Matiushkin, Oct 06 '14 at 17:59
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 06 '14 at 18:04
Add error reporting to the top of your file(s) right after your opening ` — Funk Forty Niner, Oct 06 '14 at 18:08
_$symbol because I have to in mysql, I have tables that I need to name protected names so using _ on everything. — illcrx, Oct 06 '14 at 18:15
@FélixGagnon-Grenier, no phpMyAdmin. only command line, the query worked in mysql — illcrx, Oct 06 '14 at 18:21
If `date` contains any spaces or dots etc. then try changing `WHERE date=$todaysdate` to/and quoting it `WHERE date='$todaysdate'` — Funk Forty Niner, Oct 06 '14 at 18:23

Funk Forty Niner · Accepted Answer · 2014-10-06T19:44:47.843

@Fred-ii- YOU DID IT! Can I or you make this an answer so I can vote for it? If I can I dont see how. – illcrx

Posting my comment as the answer in order to close the question.

If your date column contains any spaces or dots etc. then change WHERE date=$todaysdate
to/and quoting it WHERE date='$todaysdate'

For example: 2014-10-06 22:59:52

Would explain why you were not getting results.

However, I'm quite surprised/baffled that MySQL did not throw you a syntax error, bizarro.

score -3 · Answer 2 · answered Oct 06 '14 at 18:00

Don't have time to read your entire bit right now, but I can give you my test method from our standard mysqli execution set:

print_r($Record);

This will allow you to see the structure and possibly where your error lies.

I'll also give you our framework which can be very useful (which is why we have it! LOL). Example framework (two functions) to make it easier to use mysqli in php with two lines for each query. It also allows for CLI or web output and debugging which will dump the query (so you can run it) and shows a print_r function to show results.:

This goes at the top:

define('DEBUG', false);
define('CLIDISPLAY', false);
if (CLIDISPLAY) {
    define('PRE', '');
    define('PRE_END', '');
} else {
    define('PRE', '<pre>');
    define('PRE_END', '</pre>');
}
require_once("/etc/dbconnect.php");
$DBLink = new mysqli($VARDB_server, $VARDB_user, $VARDB_pass, $VARDB_database, $VARDB_port);
if ($DBLink->connect_errno) {
    printf(PRE . "Connect failed: %s\n" . PRE_END, $DBLink->connect_error);
    exit();
}

Be sure you have a normal php file at /etc/dbconnect.php with your credentials in it (do not put these in a web folder in case php fails one day and exposes your passwords! LOL). Note that this file can then be shared and loaded only once. It should invoke

# Sample execution
$Query = "select * from vicidial_users where user='6666' and active='Y' limit 1";
$Records = GetData($DBLink, $Query);
print_r($Records[0]); // Single record return access via [0] to access a field named "id": $Records[0]['id']
// Multiple record return access via array walking
foreach ($Records as $Record) {
    print_r($Record);
}
$Query = "update vicidial_users set active='Y' where user='6666' limit 1";
UpdateData($DBLink, $Query);

Functions (can be loaded from the credentials file or within your working file or put in a "functions.php" file and "require_once('functions.php');".

function GetData($DBLink, $Query) {
    if (DEBUG) {
        echo PRE . "Query: $Query\n" . PRE_END;
    }
    if ($Result = $DBLink->query($Query)) {
        if (DEBUG) {
            printf(PRE . "Affected rows (Non-Select): %d\n" . PRE_END, $DBLink->affected_rows);
        }
        while ($Record = $Result->fetch_assoc()) {
            $ReturnData[] = $Record;
        }
        return $ReturnData;
    } else {
        if (DEBUG) {
            printf(PRE . "Errormessage: %s\n", $DBLink->error);
            printf("Affected rows (Non-Select): %d\n", $DBLink->affected_rows);
            echo "No Records Returned\n" . PRE_END;
        }
        return false;
    }
}

function UpdateData($DBLink, $Query) {
    if (DEBUG) {
        echo PRE . "Query: $Query\n" . PRE_END;
    }
    if ($Result = $DBLink->query($Query)) {
        if (DEBUG) {
            printf(PRE . "%s\n", $DBLink->info);
            printf("Affected rows (Non-Select): %d\n" . PRE_END, $DBLink->affected_rows);
        }
        return;
    } else {
        if (DEBUG) {
            printf(PRE . "Errormessage: %s\n", $DBLink->error);
            printf("Affected rows (Non-Select): %d\n", $DBLink->affected_rows);
            echo "No Records Returned\n" . PRE_END;
        }
        return;
    }
}

You don't have time to read 10 lines, but you have time to write 100. — php_nub_qq, Oct 06 '14 at 18:02
Don't have time to read your answer, but downvoted because it’s obviously not answering the OP’s question. — Aleksei Matiushkin, Oct 06 '14 at 18:02
`I'll also give you our framework` I think you're unclear on the definition of a framework... — Félix Adriyel Gagnon-Grenier, Oct 06 '14 at 18:35
I didn't write that for this post. That was a copy/paste from something we use every day. This did not involve deciphering someone else's code, but was meant as a tool to allow him to decipher his own code. Trying to help him along his journey in php/mysql. So I got a markdown for trying to help. Hm. As far as Framework: It has a top section (to set up parameters like DEBUG) and a bottom section (functions!) and then example execution for those functions, unused portions can be deleted. In the end SQL queries can be run with two lines of code and all functionality & optional DEBUG. Framework. — TheSatinKnight, Oct 09 '14 at 04:18

How to work with data from mysql in php-multidimensional array

2 Answers2