Asked  7 Months ago    Answers:  5   Viewed   36 times

I'm coding in PHP. I have the following mySQL table:

CREATE TABLE `students` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Start` int(10) DEFAULT NULL,
  `End` int(10) DEFAULT NULL,
   PRIMARY KEY (`ID`)
 ) ENGINE=InnoDB;

I'm trying to use the mysqli_query function in PHP to DESCRIBE the table.

Here's my code:

$link = mysqli_connect($DB_HOST, $DB_USER, $DB_PASS, $DATABASE);
$result = mysqli_query($link,"DESCRIBE students");

The documentation says For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object.

But from there I don't know how to print $result so that it shows the query results. If possible I want to print $result so that it looks like:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| ID       | int(10)      | NO   | PRI | NULL    | auto_increment |
| Name     | varchar(255) | YES  |     | NULL    |                |
| Start    | int(10)      | YES  |     | NULL    |                |
| End      | int(10)      | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+ 

My other question is how to print the query SHOW CREATE TABLE students.

$result = mysqli_query($link,"SHOW CREATE TABLE students");

 Answers

35

I have to admit, mysqli_query() manual entry doesn't contain a clean example on how to fetch multiple rows. May be it's because the routine is so routine, known to PHP folks for decades:

$result = $link->query("DESCRIBE students");
while ($row = $result->fetch_assoc())
{
    // to print all columns automatically:
    foreach($row as $value) echo "<td>$value</td>";
    // OR to print each column separately:
    echo "<td>",$row['Field'],"</td><td>",$row['Type'],"</td>n";
}

In case you want to print the column titles, you have to select your data into a nested array first and then use keys of the first row:

// getting all the rows from the query
// note that handy feature of OOP syntax
$data = $link->query("DESC students")->fetch_all(MYSQLI_ASSOC);
// getting keys from the first row
$header = array_keys(reset($data));
// printing them
foreach ($header as $value) echo "<td>$value</td>";
// finally printing the data
foreach ($data as $row)
{
    foreach($row as $value) echo "<td>$value</td>";
}

Some hosts may have no support for the fetch_all() function. In such a case, fill the $data array the usual way:

$data = [];
$result = $link->query("DESC students");
while ($row = $result->fetch_assoc())
{
    $data[] = $row;
}

Two important notes I have to add.

  1. You have to configure mysqli to throw errors automatically instead of checking them for each mysqli statement manually. To do so, add this line before mysqli_connect():

     mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    
  2. The most important note: unlike mysql_query(), mysqli_query() has a very limited use. You may use this function only if no variables are going to be used in the query. If any PHP variable is going to be used, you should never use mysqli_query(), but always stick to prepared statements, like this:

     $stmt = $mysqli->prepare("SELECT * FROM students WHERE class=?");
     $stmt->bind_param('i', $class);
     $stmt->execute();
     $data = $stmt->get_result()->fetch_all();
    

It's a bit wordy, I have to admit. In order to reduce the amount of code you can either use PDO or adopt a simple helper function to do all the prepare/bind/execute business inside:

    $sql = "SELECT * FROM students WHERE class=?";
    $data = prepared_select($mysqli, $sql, [$class])->fetch_all();
Wednesday, March 31, 2021
 
Jauco
answered 7 Months ago
61

Could this be a typo? (two Ps in ppasscode, intended?)

$_POST['ppasscode'];

I would make sure and do:

print_r($_POST);

and make sure the data is accurate there, and then echo out what it should look like:

echo hash('sha256', $_POST['ppasscode']);

Compare this output to what you have in the database (manually). By doing this you're exploring your possible points of failure:

  1. Getting password from form
  2. hashing the password
  3. stored password
  4. comparison of the two.
Wednesday, March 31, 2021
 
Kwadz
answered 7 Months ago
33

Your code has 2 errors.

1.) You assign email address to $mail from form. Then access $mail instance which should be mistaken as PHPMailer instance.

$mail = $_POST['mail'];
....
....
$mail->Subject = 'Reservation';
$mail->Body    = '<b>You have a reservation!</b></br>'
....
....

2.) You are exiting from execution inside foreach

if(!empty($_POST['check_list'])) {
    foreach($_POST['check_list'] as $check) {           
        return $check;
    }
}

Fix

$name    = $_POST['name'];
$surname = $_POST['surname'];
$email   = $_POST['mail'];
$phone   = $_POST['phone'];
$address = $_POST['address'];
$services= array();

if (!empty($_POST['check_list']) && is_array($_POST['check_list'])) {
    foreach ($_POST['check_list'] as $check) {           
        $services[] = $check;
    }
}

$mail = new PHPMailer;
//other PHPMailer config options
....
....
$mail->Subject = 'Reservation';
$mail->Body    = '<b>You have a reservation!</b></br>'
.'Name: '.$name.'</br>'
.'Surname: '.$surname.'</br>'
.'Mail: '.$email.'</br>'
.'Phone: '.$phone.'</br>'
.'Address: '.$address.'</br>'
.'Services: '. implode(', ', $services);

if (!$mail->send()) {
    echo 'Message could not be sent.';
    echo 'Mailer Error: ' . $mail->ErrorInfo;
} else {
    echo 'Message has been sent';
    header('Location: index.php');
    exit();
}
Saturday, May 29, 2021
 
pocketfullofcheese
answered 5 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :