Selecting row from database

Hello i need help with selecting 1 row from a selected query, So far everything works and the query returns:

total
1280
1280
1280
1280
1280
1280

I just need one of these to save in a variable but somehow it returns 1, this is my code:

include('includes/sessionstart.php');
include 'dbh.php';
$living = "woning";

$sum_total = array();

$living_query = "SELECT total FROM costs WHERE userid='$session_id' AND category='$living'";
$living_result = mysqli_query($conn, $living_query);
$living_total = mysqli_data_seek($living_result, 1);

echo $living_total;

I have no idea why this isn’t working…

mysqli_data_seek() sets the internal pointer and returns true or false. If you actually want to get the first result, you will need to use something like mysqli_fetch_array(). I also don’t know if you need all of the rows from the query, if you only need to get 1 value, I would suggest using LIMIT 1 at the end of your SQL query to limit the results back.


$sum_total = array();

$living_query = "SELECT total FROM costs WHERE userid='$session_id' AND category='$living'";
$living_result = mysqli_query($conn, $living_query);
$living_total = mysqli_fetch_array($living_result);

echo $living_total['total'];

Jeff

1 Like

Awesome that worked very well!
Now a question that might be a bit anoying but how can you make it like a function? I need to do it on 13 categories and copy pasting is not the right way to learn things am i right? :stuck_out_tongue:

Thankyou for that very well explained answer! <3

what problems do you encounter when surrounding your code with a function definition?

Let me suggest you another approach. In case you need totals for all categories,why not to select them all at once, store in an array and then use with any category you need? With PDO it will be piece of cake:

$stmt = $pdo->prepare("SELECT category, total FROM costs WHERE userid=? GROUP BY category");
$stmt->execute([$session_id]);
$living = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// now you have totals for any category
echo $living['category1'];
echo $living['category whatever'];

Thx for the replies and very good answers, but i know nothing of PDO so i’m just using the long version for now untill i find the time to learn PDO and experiment with it.

Another question tho: Is it possible to find the percentage of each value in the array based on the total of it?

this is what i have atm:

$array_total = array($total_living, $total_care, $total_creche, $total_food_products, $total_hobby, $total_insurances, $total_loans, $total_miscellaneous, $total_power_water, $total_save, $total_taxes, $total_telecom, $total_transport);

	$total_sum = array_sum($array_total);
	print_r($total_sum);

i know nothing of PDO

I would assert that your knowledge on mysqli is no better, so it doesn’t count as a excuse. But yes, I know that PHP users prefer to stick to the most ancient and inconvenient technology they can find, so I won’t insist.

Manual way is surely quick enough:

$array_total = array($total_living, $total_care, $total_creche, $total_food_products, $total_hobby, $total_insurances, $total_loans, $total_miscellaneous, $total_power_water, $total_save, $total_taxes, $total_telecom, $total_transport);

	$total_sum = array_sum($array_total);
	print_r($total_sum);

foreach($array_total as $element) { 
   echo  ($element / $total_sum)*100 . "\n"; }

that seems to be working but there is something wrong:

all values in my array are 1 and there are 13 values so everything should return 7.69 which it does but only for the first percentage…

137.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077 7.6923076923077

Sorry, I don’t understand the problem. Everything should return 7.69, which it does. Can you expand on “but only for the first percentage”. The display you show also has the “13” at the start, from your line

print_r($total_sum);
1 Like

omg i feel so ashamed you are right i didn’t look at it like that …
Thankyou so much! :slight_smile:

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.