Help with SQL / PHP

[zer0uk]

Hi please can someone set me right, the below code isn't working I get an error "Undefined offset: 0"

example below is the problem code with error (2nd example works fine)

 // check how many rides user has done
       $resultset = $conn->query("SELECT COUNT(*)FROM tbl_rides WHERE username_id = 12");
       while($row = $resultset->fetch_assoc())
       $var_l_rides=$row[0];

  $resultset = $conn->query("SELECT `gold`FROM tbl_accounts WHERE id = 12");
       while($row = $resultset->fetch_assoc())
       $var_prem=$row['gold'];

 

[gw1500se]

Since you do no error checking after the query, that is what happens if no rows are returned due to either and error or nothing in the database meets the WHERE clause.

[mac_gyver]

21 minutes ago, zer0uk said:

fetch_assoc

the associative index for the COUNT(*) expression is actually something like $row['COUNT(*)']. to simplify this, add an alias name for the COUNT(*) expression in the query and use that alias name for the associative index.

next, both of the query examples you have shown will/probably match at most one row. why you are using a loop to fetch the data. just fetch the single row without the loop.

[zer0uk]

26 minutes ago, gw1500se said:

Since you do no error checking after the query, that is what happens if no rows are returned due to either and error or nothing in the database meets the WHERE clause.

I ran the SQL in the console and it returns 14 

[zer0uk]

15 minutes ago, mac_gyver said:

the associative index for the COUNT(*) expression is actually something like $row['COUNT(*)']. to simplify this, add an alias name for the COUNT(*) expression in the query and use that alias name for the associative index.

next, both of the query examples you have shown will/probably match at most one row. why you are using a loop to fetch the data. just fetch the single row without the loop.

Thanks for your reply  makes sense but how to I create an alias then?

Could only get this working using the code I've used do realise that their is no need for a loop, feel free to show me what I should be doing .. only been doing this for a month so learning ...

 

Thanks for your help.. appreciated

[gw1500se]

I'm confused by what you are trying to do. COUNT() returns the number of rows that match the query criteria. Is that what you really want? Why put it in a loop? The reason the 2nd one works is because it actually returns a row of data.

Edited March 16, 2020 by gw1500se

[zer0uk]

Basically its part of my membership system so the system is checking how many record the user has if its more than so many they will be denied access to add another record.

using ['COUNT(*)'] fixed the issue but if anyone wants to show me the correct way of doing this and without the loop id appreciate the learning.

[ginerjm]

I'm thinking that the error about the 0 is because you are doing a "fetch_assoc" which provides named elements rather than numeric ones.

Edited March 16, 2020 by ginerjm