Databases
StackOverflow2013
Postsdbo
POphp mysql sql select issue: syntax? structure? bind?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POphp mysql sql select issue: syntax? structure? bind?
    primarykey
    data
    text
    <p>I am having particular trouble with this code. I've redone my selects entirely using sql since my last question but I'm not getting any useful errors to help me out when I echo mysqli_error($con);</p> <p>In short, when the table is created near the bottom of the code, the echo 'contents' area is the problem. It's supposed to only echo the 'contents' area of a table called opwire if the currently logged in user's "tier" (from alternate table: members) is greater than or equal to the value of "seclevel" from the original opwire table. If it's less than, it's supposed to echo Access Denied.</p> <p>What I have now breaks the table and error checking gives nothing. Below the main table php I've also included all of functions.php. Am I missing something there in regards to $userTier; ? I can't get an echo back from that either. </p> <pre><code>&lt;?php include_once 'functions.php'; include_once 'db_connect.php'; sec_session_start(); if(login_check($mysqli) == true) { $con=mysqli_connect("localhost","myuser","mypass","mysqldb"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } function getColor($strOption) { switch ($strOption) { case "Case 1": return "#cbae80"; case "Case 2": return "#e59350"; case "Case 3": return "#b7aaa4"; } } $query= "SELECT tier FROM members WHERE id = $user_ID"; $result = mysqli_query($con,$query); $row = mysqli_fetch_array($result); $userTier = $row['tier']; $query = "SELECT category, contents, date, username FROM opwire LEFT JOIN members on opwire.userid=members.id WHERE seclevel &lt;= $userTier UNION SELECT category, 'ACCESS DENIED' AS contents, date, username FROM opwire LEFT JOIN members on opwire.userid=members.id WHERE seclevel &gt; $userTier ORDER BY date DESC"; $result = mysqli_query($con,$query); echo "&lt;table border='1'&gt; &lt;tr&gt; &lt;th&gt;Category&lt;/th&gt; &lt;th&gt;Contents&lt;/th&gt; &lt;th&gt;Date/Time&lt;/th&gt; &lt;th&gt;Operative&lt;/th&gt; &lt;/tr&gt;"; while($row = mysqli_fetch_array($result)) { echo "&lt;tr&gt;"; echo "&lt;td&gt;&lt;font size=1 color='".getColor($row['category'])."'&gt; " . $row['category'] . "&lt;/font&gt;&lt;/td&gt;"; echo "&lt;td&gt;&lt;font size=1 color=#e4d6b5&gt;" . $row['contents'] . "&lt;/font&gt;&lt;/td&gt;"; echo "&lt;td&gt;&lt;font size=1 color=silver&gt;" . $row['date'] . "&lt;/font&gt;&lt;/td&gt;"; echo "&lt;td&gt;&lt;font size=1 color=gold&gt;" . $row['username'] . "&lt;/font&gt;&lt;/td&gt;"; echo "&lt;/tr&gt;"; } echo "&lt;/table&gt;"; mysqli_close($con); } else { echo 'Access to this area requires security clearance. &lt;br/&gt;'; } ?&gt; </code></pre> <p>and functions.php, which handles all the session / user control</p> <pre><code>&lt;?php function sec_session_start() { $session_name = 'sec_session_id'; // Set a custom session name $secure = false; // Set to true if using https. $httponly = true; // This stops javascript being able to access the session id. ini_set('session.use_only_cookies', 1); // Forces sessions to only use cookies. $cookieParams = session_get_cookie_params(); // Gets current cookies params. session_set_cookie_params($cookieParams["lifetime"], $cookieParams["path"], $cookieParams["domain"], $secure, $httponly); session_name($session_name); // Sets the session name to the one set above. session_start(); // Start the php session session_regenerate_id(); // regenerated the session, delete the old one. } function login($email, $password, $mysqli) { // Using prepared Statements means that SQL injection is not possible. if ($stmt = $mysqli-&gt;prepare("SELECT id, username, password, salt FROM members WHERE email = ? LIMIT 1")) { $stmt-&gt;bind_param('s', $email); // Bind "$email" to parameter. $stmt-&gt;execute(); // Execute the prepared query. $stmt-&gt;store_result(); $stmt-&gt;bind_result($user_id, $username, $db_password, $salt); // get variables from result. $stmt-&gt;fetch(); $password = hash('sha512', $password.$salt); // hash the password with the unique salt. if($stmt-&gt;num_rows == 1) { // If the user exists // We check if the account is locked from too many login attempts if(checkbrute($user_id, $mysqli) == true) { // Account is locked // Send an email to user saying their account is locked return false; } else { if($db_password == $password) { // Check if the password in the database matches the password the user submitted. // Password is correct! $user_browser = $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user. $user_id = preg_replace("/[^0-9]+/", "", $user_id); // XSS protection as we might print this value $_SESSION['user_id'] = $user_id; $username = preg_replace("/[^a-zA-Z0-9_\-]+/", "", $username); // XSS protection as we might print this value $_SESSION['username'] = $username; $_SESSION['login_string'] = hash('sha512', $password.$user_browser); // Login successful. return true; } else { // Password is not correct // We record this attempt in the database $now = time(); $mysqli-&gt;query("INSERT INTO login_attempts (user_id, time) VALUES ('$user_id', '$now')"); return false; } } } else { // No user exists. return false; } } } function checkbrute($user_id, $mysqli) { // Get timestamp of current time $now = time(); // All login attempts are counted from the past 2 hours. $valid_attempts = $now - (2 * 60 * 60); if ($stmt = $mysqli-&gt;prepare("SELECT time FROM login_attempts WHERE user_id = ? AND time &gt; '$valid_attempts'")) { $stmt-&gt;bind_param('i', $user_id); // Execute the prepared query. $stmt-&gt;execute(); $stmt-&gt;store_result(); // If there has been more than 5 failed logins if($stmt-&gt;num_rows &gt; 5) { return true; } else { return false; } } } function login_check($mysqli) { // Check if all session variables are set if(isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) { $user_id = $_SESSION['user_id']; $login_string = $_SESSION['login_string']; $username = $_SESSION['username']; $user_browser = $_SERVER['HTTP_USER_AGENT']; // Get the user-agent string of the user. if ($stmt = $mysqli-&gt;prepare("SELECT password FROM members WHERE id = ? LIMIT 1")) { $stmt-&gt;bind_param('i', $user_id); // Bind "$user_id" to parameter. $stmt-&gt;execute(); // Execute the prepared query. $stmt-&gt;store_result(); if($stmt-&gt;num_rows == 1) { // If the user exists $stmt-&gt;bind_result($password); // get variables from result. $stmt-&gt;fetch(); $login_check = hash('sha512', $password.$user_browser); if($login_check == $login_string) { // Logged In!!!! return true; } else { // Not logged in return false; } } else { // Not logged in return false; } } else { // Not logged in return false; } } else { // Not logged in return false; } } ?&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLaurence
    1. USsylcat
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI don't see how `$user_id` will be available in `$query= "SELECT tier FROM members WHERE id = $user_ID";` It appears to be scoped to the `login` function. That said, I don't know PHP, and there may be some magic that slurps it from the session.
      singulars
      1. POphp mysql sql select issue: syntax? structure? bind?
      1. USLaurence
    2. COI'm rather new to PHP myself, most of the function.php was from tutorials. I'm following logic relative well, but I thought $user_id would be available from the functions.php include?
      singulars
      1. POphp mysql sql select issue: syntax? structure? bind?
      1. USsylcat
    3. COYou need to look at http://php.net/manual/en/language.variables.scope.php
      singulars
      1. POphp mysql sql select issue: syntax? structure? bind?
      1. USLaurence
 

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