storing value from variable in if else statement to database PHP

Question

This is a progression from last night's question, now I need to get the value from $total in my if/else statement and insert it into my database. $count['NumRows'] for price and $total for total_all... I've read about changing the value to array by using implode, but the problem is still there, my database still won't store the value... because I don't know why and I need help...

<?php
if (isset($_POST['submit'])) {
    $names      = $_POST['names'];
    $phones     = $_POST['telno'];
    $modelname  = $_POST['modelname'];
    $rentaldate = $_POST['rentaldate'];
    $numday     = $_POST['numday'];

    $sql = "INSERT INTO carbook (name, tel_no, model_name, rental_date, return_date, no_of_days) VALUES ('$names', '$phones', '$modelname', '$rentaldate', '$returndate', '$numday')";

    if ($conn->query($sql) == true) {
        echo "Name: $names";
        echo "<br/>Phones: $phones";
        echo "<br/>Car Name: $modelname";
        echo "<br/>Rental Date: $rentaldate";
        echo "<br/>Return Date: $returndate";
        echo "<br/>No Of Days: $numday";
        echo "<br/>";
    } else {
        echo "Error: $sql<br>$conn->error";
    }

    if ($modelname && $numday) {

        if ($modelname = 'Jetta') {

            $countrows = 'SELECT price_day AS NumRows FROM carlist WHERE carBookId = 1';
            $result    = $conn->query($countrows);
            $count     = $result->fetch_assoc();
            $total     = $count['NumRows'] * $numday;

            $total = number_format($total, 2);

            echo 'Price per day: RM<b>' . $count['NumRows'] . '</b>.<br/>';
            echo 'Total Price: RM<b>' . $total . '</b>.';

            $sql = "INSERT INTO carbook (price, total_all) VALUES ('$total', '$countrows')";

There's no error, but the database didn't recognize the value, and it still inserts 0 in my table.

edited this is my table structure
table structure

Is this query supposed to be countin rows where `carBookId` is one? `SELECT price_day AS NumRows FROM carlist WHERE carBookId = 1'` — RiggsFolly, Jun 09 '17 at 08:46
Firstly try changing `if ($modelname = 'Jetta') {` to `if ($modelname == 'Jetta') {` I think you want to compare `$modelname` to `'Jetta'` not set `$modelname`'s value. — Ethan, Jun 09 '17 at 08:47
@RiggsFolly that is suppose to fetch the price in the database table — Hensemnm, Jun 09 '17 at 10:36
Then the query is very misleading. When you copy stuff, make it make sense in its new situation. `SELECT price_day AS NumRows` makes it look like you are trying the get a row count and not just get a price — RiggsFolly, Jun 09 '17 at 12:46
@RiggsFolly yes i admit i copy some of that while learning all through it.. and so i am open to suggestion.. — Hensemnm, Jun 09 '17 at 16:08

B. Desai · Answer 1 · 2017-06-09T12:27:20.363

0

You are paasing wrong variables in insert query

$numRows = $count['NumRows'];
$sql = "INSERT INTO carbook (price, total_all)
                            VALUES ('$numRows','$total' )";

EDIT

From your table structure I can see there may be 2 issues :

1) In your table there are other fields also which doesn't have default values as well as there must not be null So also append those fields in insert query.

2) Do not enclose value in single quotes for int or double data types

edited Jun 09 '17 at 12:27

answered Jun 09 '17 at 08:47

B. Desai

16,414
5
26
47

i use the coding u gave,no error and i think its good but its still giving 0 in my database... is it posible for table in my database to affect my coding? – Hensemnm Jun 09 '17 at 10:43
show your table structure. Also you are getting result in `echo 'Price per day: RM'. $count['NumRows'] . '.
'; echo 'Total Price: RM'. $total . '.';` ? – B. Desai Jun 09 '17 at 10:52
i have uploaded my table structure, yes the result are shown, no error so far, just the database still fecthing 0 – Hensemnm Jun 09 '17 at 12:07
i have a hard time following your 1st suggestion...can u explain it on more understandable term for me and how to append those fields in insert query. the result still coming out 0... – Hensemnm Jun 09 '17 at 15:29

rbaskam · Answer 2 · 2017-06-09T15:33:54.360

0

You are trying to insert the string (query) in the variable $countrows into your total_all field. Your datatype in your database is probably interpreting this string incorrectly and inserting a 0. You need to replace the the $countrows with the alias you have created in the query.

$numberOfRows = $count['NumRows'];
$sql = "INSERT INTO carbook (price, total_all) VALUES ('$numberOfRows', '$total')";

EDIT: This worked for me

$numberOfRows = $count['NumRows'];
$total     = $numberOfRows * 3;
$totalFormatted = number_format($total, 2);

echo 'Price per day: RM<b>' . $numberOfRows . '</b>.<br/>';
echo 'Total Price: RM<b>' . $totalFormatted . '</b>.';
$sql = mysql_query("INSERT INTO carbook (price, total_all) VALUES ('$total', '$numberOfRows')");

edited Jun 09 '17 at 15:33

answered Jun 09 '17 at 09:00

rbaskam

749
7
22

You said in your title you wanted this $count['NumRows'] for price and $total for total_all but in your code you have done this. Are you sure you have them in the right order INSERT INTO carbook (price, total_all) VALUES ('$total','$countrows' )" does it need to be thisINSERT INTO carbook (price, total_all) VALUES ('$countrows','$total' )" – rbaskam Jun 09 '17 at 10:48
yep ive been wondering about that, because i make the query inside the if statement... i dont know whether its working or not because no error shown here $sql = "INSERT INTO carbook (price, total_all) VALUES ('$numRows','$total' )"; – Hensemnm Jun 09 '17 at 12:11
So firstly do the echos above the INSERT output the correct values? If they are correctly outputting it then the values are not the problem. What are the datatypes on each column in the Database? – rbaskam Jun 09 '17 at 12:20
i edited and already uploaded the table structure aboved, tell me anything u found weird ok... – Hensemnm Jun 09 '17 at 12:22
Seems OK but read this when you have time https://stackoverflow.com/questions/6831217/double-vs-decimal-in-mysql. Can you let me know what the output of the two echo statements above your INSERT are please? The echo 'Price per day: RM' . $count['NumRows'] . '.
'; AND echo 'Total Price: RM' . $total . '.'; – rbaskam Jun 09 '17 at 12:26
Also have you switched your VALUES around as they are in the wrong order so my answer above but change to this ($total, '$numberOfRows') – rbaskam Jun 09 '17 at 12:29
Price per day: RM13300. Total Price: RM39,900.00. price per day for $numRows and total price for $total – Hensemnm Jun 09 '17 at 14:23
Ok firstly you are trying to insert a number_format variable into a double so this contains a comma which will break it. So either move the insert before or use a different variable name for the number formatted section. I have replicated this locally and it now works. – rbaskam Jun 09 '17 at 14:45
If you need an example here it is. Make sure you have your values in the correct order. $numberOfRows = 13300.1; $total = $numberOfRows * 3; $sql = mysql_query("INSERT INTO carbook (price, total_all) VALUES ('$total', '$numberOfRows')"); $total = number_format($total, 2); echo 'Price per day: RM' . $numberOfRows . '.
'; echo 'Total Price: RM' . $total . '.'; – rbaskam Jun 09 '17 at 15:12
I've updated my answer with the formatting and the correct code that works – rbaskam Jun 09 '17 at 15:19
i will try that and i will update you later thanks in advance mate , brilliant helping hand ever – Hensemnm Jun 09 '17 at 15:39
emmm...the problem still persist, do i need to change something in the table to make it work? eg, type,name etc... – Hensemnm Jun 09 '17 at 16:12
Shouldn’t have to. I created a table to match yours from the image and used the exact code I posted above. – rbaskam Jun 09 '17 at 18:32
it still fetching 0 somehow...do i have to free result from earlier query? – Hensemnm Jun 09 '17 at 19:42
I am not sure. What you should do is remove the code I put in the edit into a simple PHP page and change the $count[‘NumRows’] to 13300 and then run it. If it works then it’s a problem in your code if not then something else. I have it working on my local with that EDIT I put in. – rbaskam Jun 09 '17 at 19:46
ive tried changing $numberofRows to 13000, its still the same...so its something else then... – Hensemnm Jun 09 '17 at 20:25
http://imgur.com/a/GURy3 Here is exactly what I am doing and its working. Create your self a basic page and just test that it isn't your DB first as shown in the images in the link. If this works and it saves then its your code else its your DB and you need to investigate into that. – rbaskam Jun 12 '17 at 10:12

storing value from variable in if else statement to database PHP

2 Answers2