MySQL Joining Player Info with Times

Question

I'm using this query to return all profile info of a player and also all of the times associated with that player from the EVENT_DATA table. However, I'm only being returned one event time per player even though each player has more than one time in the database. The player's name is being used in a search box, and that's why the WHERE clause includes the name entered into the search. Does anyone recommend that I run two separate queries; one for the profile and one for the times? The profile data comes up ok, but it's just the event data only being limited to one row. This is my code, and I appreciate any suggestions.

     <form class='form-inline quick-search-form' role='form'>
        <div class='form-group'>
                <input type='text' id='name' name='name' class='form-control' placeholder='Driver name'>
        </div>
        <button type='submit' id='quick-search' class='btn btn-custom'><span class='glyphicon glyphicon-search custom-glyph-color'></span></button>
      </form>




$sql = "SELECT EVENT_DATA.personaId, EVENT_DATA.ID AS event_id, EVENT_DATA.alternateEventDurationInMilliseconds, EVENT_DATA.EVENTID, EVENT_DATA.rank, EVENT_DATA.carId, PERSONA.iconIndex, PERSONA.cash, PERSONA.level, PERSONA.created, PERSONA.score, PERSONA.motto, PERSONA.repAtCurrentLevel, PERSONA.rep, PERSONA.name AS p_name
FROM EVENT_DATA
INNER JOIN PERSONA ON EVENT_DATA.personaId = PERSONA.ID


WHERE (PERSONA.name = '".@mysqli_real_escape_string($conn, $_GET['name'])."' AND EVENT_DATA.EVENTID = '43' AND EVENT_DATA.alternateEventDurationInMilliseconds > '0')";


if ($result = mysqli_query($conn, $sql)){

    if(mysqli_num_rows($result) > 0)  { 

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


$name = $row['p_name'];
$avatarimg = mysqli_real_escape_string($conn, $row['iconIndex']);
$cash = mysqli_real_escape_string($conn, $row['cash']);
$name = mysqli_real_escape_string($conn, $row['p_name']);
$level = mysqli_real_escape_string($conn, $row['level']);
$createddate = mysqli_real_escape_string($conn, $row['created']);
$driverscore = mysqli_real_escape_string($conn, $row['score']);
$motto = mysqli_real_escape_string($conn, $row['motto']);
$repcurrent = mysqli_real_escape_string($conn, $row['repAtCurrentLevel']);
$reptotal = mysqli_real_escape_string($conn, $row['rep']);
$personaid = $row['personaId'];
$eventid = $row['EVENTID'];
$milliseconds = $row['alternateEventDurationInMilliseconds'];

Then, I used php echo for each of the variables, and outputted them in a table.

Thank you!

Please add some sample data / table definitions and current output so we can see how the tables are structured. It will help provide you with a better answer. — SteveB, Sep 22 '18 at 18:46
why you have @mysqli_real_escape_string($conn... and not mysqli_real_escape_string($conn... whit out @?? — ScaisEdge, Sep 22 '18 at 18:49
But it looks like I'm only being returned the newest value from the EVENT_DATA table for each player instead of all times for that player. Still diagnosing the problem. — ccc, Sep 22 '18 at 20:12
No. I don't recommend that. In general, the fewer round trips to the database, the better. However, I think you should improve your method for constructing queries. See about prepared and bound queries. — Strawberry, Sep 22 '18 at 22:29

Parfait · Accepted Answer · 2018-09-23T06:49:21.893

0

According to the JOIN, your query should return multiple values assuming one-to-many relationship between Persona and Event_Data. The issue is you are saving values in PHP scalar variables via a loop, so only the last value iterated in loop is stored.

Simply save data in PHP arrays not scalar variables or build your HTML table while in the fetch loop. And of course use parameterization as a malicious user can inject harmful code in search input (i.e., Bobby Tables).

# SQL WITH QMARK PLACEHOLDER (USING TABLE ALIASES)
$sql = "SELECT e.personaId, e.ID AS event_id, e.EVENTID, e.rank, e.carId, 
               e.alternateEventDurationInMilliseconds, 
               p.iconIndex, p.cash, p.level, p.created, p.score, p.motto, 
               p.repAtCurrentLevel, PERSONA.rep, p.name AS p_name
        FROM EVENT_DATA e
        INNER JOIN PERSONA p ON e.personaId = p.ID
        WHERE (p.name = ? AND e.EVENTID = '43' 
          AND  e.alternateEventDurationInMilliseconds > '0')";

# INITIALIZE ARRAYS - TWO METHODS: array() or []
$name = array(); $avatarimg = []; $cash = array(); $level = array(); $createddate = [];
$driverscore = array(); $motto = []; $repcurrent = array(); $reptotal = []; 
$personaid = array(); $eventid = []; $milliseconds = array();

# PREPARED STATEMENT
$stmt = mysqli_prepare($conn, $sql);

# BIND PARAM 
$param = mysqli_real_escape_string($conn, $_GET['name']);
mysqli_stmt_bind_param($stmt, "s", $param);

# EXECUTE STATEMENT
if (mysqli_stmt_execute($stmt)){
    $result = $stmt->get_result();
    # CHECK ROWS 
    if(mysqli_num_rows($result) > 0)  { 
       # ITERATE THROUGH ROWS
       while ($row = mysqli_fetch_array($result)){
            # APPEND TO ARRAYS
            $name[] = mysqli_real_escape_string($conn, $row['p_name']);
            $avatarimg[] = mysqli_real_escape_string($conn, $row['iconIndex']);
            $cash[] = mysqli_real_escape_string($conn, $row['cash']);
            $level[] = mysqli_real_escape_string($conn, $row['level']);
            $createddate[] = mysqli_real_escape_string($conn, $row['created']);
            $driverscore[] = mysqli_real_escape_string($conn, $row['score']);
            $motto[] = mysqli_real_escape_string($conn, $row['motto']);
            $repcurrent[] = mysqli_real_escape_string($conn, $row['repAtCurrentLevel']);
            $reptotal[] = mysqli_real_escape_string($conn, $row['rep']);
            $personaid[] = $row['personaId'];
            $eventid[] = $row['EVENTID'];
            $milliseconds[] = $row['alternateEventDurationInMilliseconds'];    
       }    
    }    
}

edited Sep 23 '18 at 06:49

answered Sep 22 '18 at 23:35

Parfait

104,375
17
94
125

Hey, I appreciate the help man and I definitely need to use prepared statements. However, I keep getting errors that say "Only variables should be passed by reference" and " mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt". Since I'm not as familiar with these statements, I'm still debugging how to make the above solution work. – ccc Sep 23 '18 at 04:48
See update as `bind_param` expects only variables passed and not literal values like your `$_GET` value. – Parfait Sep 23 '18 at 06:51
Hmm, I updated bind_param below, but I still get these errors: "mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given" and "mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given" $param=mysqli_real_escape_string($conn, $_GET['name']); mysqli_stmt_bind_param($stmt, "s", $param); – ccc Sep 23 '18 at 07:17
This assumes `$conn` is properly initialized (not included here). Wrap [error handling](https://stackoverflow.com/questions/2228430/mysqli-throws-warning-mysqli-stmt-bind-param-expects-parameter-1-to-be-mysql) if needed. – Parfait Sep 23 '18 at 15:08
I don't get any errors with this statement while connecting, but this is what I have: ```$conn = new mysqli('localhost', 'username', 'password', 'database'); if($conn->connect_errno > 0) { die('Unable to connect to database [' . $conn->connect_error . ']'); }``` – ccc Sep 23 '18 at 16:43
Alright, I fixed the problem. This may be a very ridiculous question, but how do I execute this query now? Previously, I would Echo the variables. Is there a safer way to do this with my new Prepared Statement? – ccc Sep 23 '18 at 18:23
Prepared statements are server side methods and protect against input not output. Echoing (i.e., outputting) is not a security issue as users are not involved. With that said, PHP has frameworks to interact data between server and client including [MVC](https://en.m.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller) operations. But that's beyond this specific question. – Parfait Sep 23 '18 at 19:05
Well, I tried doing something like this but I received this error "Uncaught Error: Call to a member function fetch() on array" ```echo ""; while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $eventid = $row['EVENTID']; $personaid = $row['personaId']; echo ""; } echo "

Event ID Persona ID
" . $eventid . " " . $personaid . "
";``` I was also thinking about somehow outputting the array with a foreach loop: ```foreach ($result as $stmt) { }``` – ccc Sep 23 '18 at 19:11
You're using PDO now (a different DB-API)? Please ask a different question if using a different implementation. Haven't we solved your immediate issue of single value output? BTW - I have tested this above solution with `mysqli` using my DB data and loop should work for your echo calls. – Parfait Sep 23 '18 at 19:25
In terms of single value output, yes. Thanks for all your help! I'll make another question then in terms of the output (since I'm only being returned values when printing them with print_r . – ccc Sep 23 '18 at 19:29

MySQL Joining Player Info with Times

1 Answers1