PHP MYSQL, two tables

Question

I have two tables and i want to echo the total call count once each user logins:

Login

Firstname | Lastname | Login ID
------------------------------
Tyler     | Durden    | 3

Call Count

Name        | Call Count | Open       | GrandTotal
------------------------------
Tyler Durden| 100        |    33      | 133

i tried:

 <?php

 $result = mysqli_query($mysqli, "SELECT * FROM csvdata WHERE Name=".$_SESSION['firstname']. ' ' .$_SESSION['lastname']." ");

while($res = mysqli_fetch_array( $result )) {

echo $res['Open'].' Open Calls';
echo $res['GrandTotal'].' Grand Total Calls';

 } 
 mysqli_close($mysqli);
?>

But its not working, i think i have to join the the two tables to get it to work. What do you think?

Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — u_mulder, Mar 14 '17 at 20:41
**Warning**: You're open to mysql injection, use prepared statements to prevent that. [**Here**] are the docs about how to use prepared statements with `mysqli_` — Nytrix, Mar 14 '17 at 20:41
There are other issues, but why don't you use Login ID instead of Name in the Call Count table? — Don't Panic, Mar 14 '17 at 20:50
The beauty of this API is that it affords the use prepared and bound statements — Strawberry, Mar 14 '17 at 20:51
@Nytrix [here](http://php.net/manual/en/mysqli.prepare.php#refsect1-mysqli.prepare-examples)? ;) — Don't Panic, Mar 14 '17 at 20:52
@Don'tPanic Woopss, forgot to attach the link...But, *yes* that is the link I was supposed to attach. — Nytrix, Mar 14 '17 at 20:53
Say hello to [little Bobby Tables](http://bobby-tables.com/) — alexis, Mar 14 '17 at 21:09

score 0 · Accepted Answer · answered Mar 14 '17 at 20:45

Assuming your Call Count table is actually called csvdata, you'll want to format your SQL request string a bit by adding single quotes around the WHERE name = part.

<?php

    $result = mysqli_query($mysqli, "SELECT * FROM csvdata WHERE Name='".$_SESSION['firstname']. ' ' .$_SESSION['lastname']."' ");

    while($res = mysqli_fetch_array( $result )) {

        echo $res['Call Count'].' Call Count';
        echo $res['Open'].' Open Calls';
        echo $res['GrandTotal'].' Grand Total Calls';

    } 
    mysqli_close($mysqli);
?>

score 0 · Answer 2 · answered Mar 14 '17 at 21:06

Good practice would require that you use primary keys to facilitate joins between tables and make sure two users with the same name can be differenciated.

In that case you may want to consider replacing the Name column in your Call Count table for your loginID. This way you could get your name from the Login table (as shown below). Also, as bad as it is to have duplicated data like your user's name in both tables, you do not need your GrandTotal column since you can easily get the sum of CallCount and Open to get the exact same number. In the end, your query should look more like this (assuming your tables are called Login and CallCount).

<?php
    $result = mysqli_query($mysqli, "SELECT l.FirstName, l.LastName, cc.CallCount, cc.Open, (cc.CallCount + cc.Open) AS GrandTotal FROM Login AS l JOIN CallCount AS cc ON l.LoginID = cc.LoginID WHERE l.FirstName LIKE \"".$_SESSION['firstname']."\" AND l.LastName LIKE \"".$_SESSION['lastname']."\"");

    // ...
?>

PHP MYSQL, two tables

2 Answers2