Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Firstly, while your input is from a database, you still should never trust any data you didn't type yourself. </p> <pre><code>$result = $db-&gt;query("SELECT id, username, password FROM users WHERE email = $email"); $row = $result-&gt;fetch(); </code></pre> <p>This has a Syntax Error: <code>$email</code> requires quotes, as it is a string.</p> <p>Are you really sure you're connecting to <code>$_SERVER["SERVER_NAME"]</code>?<br> You should really connect to just <code>localhost</code> instead...</p> <p>A tip for testing your queries is to wrap it in <code>if</code> blocks, because if it fails, <code>FALSE</code> is returned instead of an object. You can't call <code>false-&gt;fetch()</code>.</p> <pre><code>if ($result = $db-&gt;query($sql)) { //do stuff, fetch data, etc. } else { if (DEBUG) { // define('DEBUG', 1); trigger_error("Query Failed; Fix it: " . htmlspecialchars($sql)); } else { header("Location: /oops.php"); } } </code></pre> <p>Also, use a <a href="http://php.net/manual/en/mysqli.prepare.php" rel="nofollow"><code>prepare</code></a> for this. In fact, use a prepare for everything that inserts data, or includes user input, period. And define it <strong>before</strong> any loops.</p> <pre><code>if ($x = $db-&gt;prepare("SELECT id, username, password FROM users WHERE email = ?")) { $x-&gt;bind_param("s", $email); $x-&gt;execute(); $x-&gt;bind_result($id, $user, $pass); $x-&gt;fetch(); } </code></pre> <p>Fetch doesn't do exactly what you are used to. It fetches data after a query has been executed. <code>fetch_assoc</code> will fetch an associative array from a result object.</p> <p>In addition, querying your database for <code>users.id = 1</code> every single time anyone loads a page is not a good idea. Stick that in the setup script. And make the anonymous user have an impossible password (aka instead of <code>$2y$13$&lt;hash&gt;</code> it would be <code>Nope, not happenin'</code> or something).</p> <p>Also, you are echoing into HTML directly from the database. If you dont sanitize, I could create a username like <code>&lt;script src=""&gt;&lt;/script&gt;</code> and have a lot of fun with the latest exploits. Sanitize~</p> <p><strong>UPDATE</strong>: After reading your code, I found your issue.</p> <pre><code>$stmt1 = $db-&gt;prepare("SELECT wins FROM users WHERE id = ?"); $stmt2 = $db-&gt;prepare("SELECT losses FROM users WHERE id = ?"); $stmt1-&gt;bind_param($_SESSION["id"]); $stmt2-&gt;bind_param($_SESSION["id"]); $stmt1-&gt;execute(); $stmt2-&gt;execute(); $row1 = $stmt1-&gt;fetch(); $row2 = $stmt2-&gt;fetch(); </code></pre> <p>There's three issues here<sup>1</sup>.</p> <p>First issue is that <strong><em>you can only have one prepare call on a given connection at a time</em></strong>. I'm fairly certain this is because it's a blocking API call. Correct me if I'm wrong.</p> <p>The second is that <strong><em>you do not specify a type for the <code>bind_param</code> call</em></strong>. You must assign a type to anything you specify here. <code>s</code> = string, <code>i</code> = integer. </p> <p>The third is that <strong><em>you do not bind any result variables here at all</em></strong>. You <strong>must</strong> bind variables when using a prepared query. This is a fix for this instance:</p> <pre><code>if ($query = $db-&gt;prepare("SELECT wins, losses FROM users WHERE id = ?")) { $query-&gt;bind_param("s", $_SESSION["id"]); $query-&gt;execute(); $query-&gt;bind_result($wins, $losses); if (!$query-&gt;fetch()) { // fetch one row from database. logout(); // or equiv. } } else { if (DEBUG) { echo "Malformed SQL or In-use thread. SQL: &lt;sql here&gt;"; } else { header("Location: /oops.php"); } } </code></pre> <p><sup>1. Or 4 if you count doing this with multiple queries.</sup></p>
    singulars
    1. This table or related slice is empty.
    1. POTrying to learn mysqli, none of my statements are actually returning results
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAmelia
    1. USAmelia
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POTrying to learn mysqli, none of my statements are actually returning results
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COMy problem is that it should return values for the queries. It's not, and an if statement won't change that. Also, I use prepare statements throughout, but I've been told if it's with static, literal data there's no point in having it as a prepare statement.
      singulars
      1. PO
      1. USuser212541
    2. CO`fetch_assoc` still messes up.
      singulars
      1. PO
      1. USuser212541
    3. CO@user212541 prepares should be used anywhere you insert unsafe data. also judging my your previous question, this is an issue with your MySQL install or PHP itself. You are getting errors from perfectly good code. Also, what `fetch_assoc`? I never mentioned it :). Also, an important note is to **check the value that fetch returns**. `NULL` = no data, `FALSE` = Error, `TRUE` = a row returned.
      singulars
      1. PO
      1. USAmelia
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload