2

I have the following query which is valid when querying the database:

EDIT 3: Per Joe Swindell's advice, it could be that the SET @runningTotal := 0 line in the query. The issue to address is finding a way to convert the complex query below into a JSON string which will also save the the NumPosts part. NumPosts is a running total which is dependent on the SET line; removing the set line removes the mysqli_fetch_array error, but results in NULL for all NumPost values.

EDIT 2: I have echoed the query below and pasted back into MySQL to confirm that it is working, and it is in fact valid.

SET @runningTotal := 0;
SELECT T.ForDate,  (@runningTotal := @runningTotal + T.DayProgress) as NumPosts
FROM
(

    SELECT DATE(  completed_courses.complete_date ) AS ForDate, COUNT( * ) AS DayProgress
    FROM users
    INNER JOIN completed_courses ON users.user_id = completed_courses.user_id
    INNER JOIN wg_courses ON wg_courses.c_id = completed_courses.c_id
    INNER JOIN workgroup ON wg_courses.wg_id = workgroup.g_id
    WHERE users.course_group LIKE  '4h%' AND completed_courses.complete_date > 0

    GROUP BY DATE(completed_courses.complete_date)
) T
ORDER BY T.ForDate

The above outputs:

ForDate     NumPosts
2014-07-29  950
2014-07-30  3063
2014-07-31  3669
2014-08-01  4584
2014-08-02  5088
...

EDIT: Checked the connection and made sure everything matches, and this is the output: "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given"

PHP code:

$data = array();
$i = 0;
while($row = mysqli_fetch_array($con, $result)) {

    $i += 1;
    array_push($data, array($i) + $row);
}

Later, the code is encoded into JSON for use in Google Charts:

var data = <?php echo json_encode($data) ?>

In addition, exporting the query results as SQL, CSV, Excel files, etc. results in all NumPosts values as NULL:

ForDate     NumPosts
2014-07-29  NULL
2014-07-30  NULL
2014-07-31  NULL
2014-08-01  NULL
2014-08-02  NULL
...

What could be the reason the values are not saving or fetching properly?

Improve this question
4
  • Where is your PHP code? Please add it to the question Commented Nov 21, 2014 at 15:34
  • If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and is being removed in future versions of PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way can help explain best practices. Always be absolutely sure your user parameters are properly escaped or you will have severe SQL injection bugs. Commented Nov 21, 2014 at 15:38
  • Your query is failing. Where is the code in php that generates/runs your query? Commented Nov 21, 2014 at 15:42
  • Oops, I meant mysqli_fetch_array. Basically, I'm trying to convert a query to JSON for inclusion in Google Charts, but the data is giving a null. Commented Nov 21, 2014 at 15:42

3 Answers 3

Reset to default
1

http://php.net/manual/en/mysqli-result.fetch-array.php

You are using mysqli_fetch_array() incorrectly.

If that is where you are attempting to make your query, you need to do a lot of restructuring. If you've already made your query and stored it in say $result:

You'd use 

mysqli_fetch_array($result, MYSQLI_ASSOC);

After you've made a connection and query

Your entire code might look something like this:

$query = "SELECT Whatever whatever from something;";
$result = mysqli_query($con, $query);       

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

    $i += 1;
    array_push($data, array($i) + $row);
}

EDIT: As it stands you have multi query. Run a single query of SET @runningTotal := 0; THEN reset your query variable to the rest of the query and run that, you will then get results.

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

7 Comments

or use directly the: mysqli_fetch_assoc function.
I implemented Joe's suggestion, and it gave a boolean error: "mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given". Something here is being evaluated as false...
That would indicate your query is failing.
Try putting in an 'echo $query' after you define $query. Copy and paste that echoed query into mysql, see what's going on.
It's your set line. That makes your query a multi query, not a single query.
|
0

You have parameter one as your connection, so there may be nothing wrong with your query but the (probably earlier) executed mysqli_connect() fails and returns false (the boolean which is mentioned in your error)

You should go ahead and check that part

Improve this answer

2 Comments

I checked the db connection and made sure the credentials were correct, and got: "Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given".
You got totaly wrong parameters to your function. Check this: w3schools.com/php/func_mysqli_fetch_array.asp i recommend to use the assoc result type
-2

You need to rewrite your mysqli_fetch_array as mentioned below and also array_push will be rewritten as, array_push manual here.

Numeric array

    $data = array();
    while($row=mysqli_fetch_array($result,MYSQLI_NUM)) {
        printf ("%s (%s)\n",$row[0],$row[1]);
        array_push($data, $row);
    }

Associative array

    $data = array();
    while($row=mysqli_fetch_array($result,MYSQLI_ASSOC)){

       printf ("%s (%s)\n",$row["ForDate"],$row["NumPosts"]);
       array_push($data, $row);
    }
Improve this answer

2 Comments

OOPs, Misunderstand the question.
delete your answer then :) at least you will get back your 4 points :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.