Trouble connecting with my database

Hi everyone !!

I am reading Kevin Yang’s book ‘Build Your Own Database Driven Website Using PHP and SQL’ and I have trouble with some code in chapter 7. We are trying to establish an admin content management system to manage a list a jokes, authors and their categories. The book is a bit old and uses old syntax that I am not used to, so I tried on my own but got some problem. Here, we are trying to display the list of authors contained in my database and add a edit/delete button. I get ‘Error fetching authors from the database: No database selected’ when I open ‘Manage authors’.
Here is the code:

index.php

[CODE]<?php
include_once $_SERVER[‘DOCUMENT_ROOT’]. ‘/includes/magicquotes.inc.php’;
include $_SERVER[‘DOCUMENT_ROOT’]. ‘/includes/db.inc.php’;

$result = mysqli_query($link, ‘SELECT id, name FROM author’);
if (!$result)
{
$error = 'Error fetching authors from the database: ’ . mysqli_error($link);
include ‘error.html.php’;
exit();
}

foreach ($result as $row){

$authors[] = array('id' =>$row['id'], 'name' => $row['name']);

}

include ‘authors.html.php’

?>[/CODE]

authors.html.php

[CODE]<?php include_once $_SERVER['DOCUMENT_ROOT'] .'/includes/helpers.inc.php'; ?>

Manage Authors

Manage Authors

Add new author

    <?php foreach ($authors as $author): ?>
  • <?php htmlout($author['name']); ?>
  • <?php endforeach; ?>

Return to JMS home

[/CODE]

db.inc.php

[CODE]<?php
ini_set(‘display_errors’, 1); // displays errors

$link = mysqli_connect(‘localhost’, ‘root’, ‘password’);
if (!$link)
{
$error = ‘Unable to connect to the database server.’;
include ‘error.html.php’;
exit();

if (!mysqli_set_charset($link, ‘utf8’))
{
$output = ‘Unable to set database connection encoding.’;
include ‘output.html.php’;
exit();
}

if (!mysqli_select_db($link, 'ijdb'))    

{
$error = ‘Unable to locate the joke database.’;
include ‘error.html.php’;
exit();
}

} ?> [/CODE]

(Here I have not put my right password)
Thank you

nevermind I got it

Don’t leave us in suspense. What was it?

1 Like

I forgot to connect with my database, as I said in the title :sweat_smile:

I forgot that peace of code in my controller :

if (!mysqli_select_db($link, 'ijdb')) { $error = 'Unable to locate the joke database.'; include 'error.html.php'; exit(); }

But I have another question, a bit more complex now …! As I said, the author is using old syntax so I try to copy his code with the new one but it is pretty difficult. Now, we are trying to delete a certain author in the DMS, and by the same token delete all of the jokes he created and also all of the categories that those jokes are related to… Here is his code of the controller (old syntax):

[CODE]if (isset($_POST[‘action’]) and $_POST[‘action’] == ‘Delete’)
{
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;
// Get jokes belonging to author
try
{
$sql = ‘SELECT id FROM joke WHERE authorid = :id’;
$s = $pdo->prepare($sql);
$s->bindValue(‘:id’, $_POST[‘id’]);
$s->execute();
}

catch (PDOException $e)
{
$error = ‘Error getting list of jokes to delete.’;
include ‘error.html.php’;
exit();
}
$result = $s->fetchAll();
// Delete joke category entries
try
{
$sql = ‘DELETE FROM jokecategory WHERE jokeid = :id’;
$s = $pdo->prepare($sql);
// For each joke
foreach ($result as $row)
{
$jokeId = $row[‘id’];
$s->bindValue(‘:id’, $jokeId);
$s->execute();
}
}
catch (PDOException $e)
{
$error = ‘Error deleting category entries for joke.’;
include ‘error.html.php’;
exit();
}
// Delete jokes belonging to author
try
{
$sql = ‘DELETE FROM joke WHERE authorid = :id’;
$s = $pdo->prepare($sql);
$s->bindValue(‘:id’, $_POST[‘id’]);
$s->execute();
}
catch (PDOException $e)
{
$error = ‘Error deleting jokes for author.’;
include ‘error.html.php’;
exit();
}

// Delete the author
try
{
$sql = ‘DELETE FROM author WHERE id = :id’;
$s = $pdo->prepare($sql);
$s->bindValue(‘:id’, $_POST[‘id’]);
$s->execute();
}
catch (PDOException $e)
{
$error = ‘Error deleting author.’;
include ‘error.html.php’;
exit();
}
header(‘Location: .’);
exit();
}[/CODE]

And here is what I tried to do:

[CODE]if(isset($_POST[‘action’]) and $_POST[‘action’]== ‘Delete’){

include $_SERVER['DOUCMENT_ROOT']. '/includes/db.inc.php';

$id = $_POST['id'];
$sql = "SELECT id FROM joke WHERE authorid = '$id'";

if (!mysqli_query($link, $sql))    
    {    
    $error = 'Error selecting authorid: ' . mysqli_error($link);    
    include 'error.html.php';    
    exit();    
    }  
$result1 = mysqli_fetch_array($sql);

$deletecategory = "DELETE FROM jokecategory WHERE jokeid= '$jokeId ' " ;

    foreach ($result1 as $row){
    $jokeId = $row['id'];

 }


if(!isset($deletecategory)){
    $error = 'Error deleting category entries for joke.';
    include 'error.html.php';
    exit();}


$deletejoke = "DELETE FROM joke WHERE authorid = '$id' ";

if(!isset($deletejoke)){
$error = 'Error deleting joke from author.';
include 'error.html.php';
exit();}

$deleteauthor = “DELETE FROM author WHERE authorid = ‘$id’”;
if(!isset($deleteauthor)){
$error = ‘Error deleting the author.’;
include ‘error.html.php’;
exit();}

header('Location: .');
exit();

}

?>[/CODE]

Thank you very much

You’ve actually gone the wrong way here - you’ve taken the authors code using PDO and prepared statements, and changed it to use mysqli but dumped all the prepared statements out of it and reverted to concatenating your variables into the query. There’s nothing wrong with wanting to use mysqli in preference to PDO, but removing prepared statements makes no sense to me. In this case, I’d say that prepared statements are a more modern, more secure method than concatenating strings into queries. The author even demonstrates how you can prepare the statement once, then execute it multiple times with different values.

Why don’t you want to stick with the PDO and prepared statements that the book is teaching? Or is it the try/catch structure you are removing?

I’ve spotted one error. You use mysqli_fetch_array() where the author uses PDO->fetchAll(). These two statements have different effects - fetchAll() fetches all rows and puts them into an array, which you can then iterate through in the foreach() loop, but mysqli_fetch_array() only recovers one row. So your foreach() loop will only delete one of the jokes, unless you add some code to create an array containing all the joke ids by calling mysqli_fetch_array() for each result. I believe there is an equivalent to fetchAll() in mysqli, but I think it only works with certain drivers.

EATA - oh hang on, there’s another error. You keep creating queries, but you never execute them. The only query you actually execute here is the first one, to retrieve all the joke ids. For all the rest, you just create the query string, then check to see if the string exists (which of course it does, because you just created it) but don’t do anything else.

Thank you a lot for your answer @droopsnoot !

I was using a version of the book that used mysqli statements and then switched to a version using PDO statements, so I did not want to change all my code to PDO which I did not know. I have now re read chapter four about PDO and prepared statements and I think I understand. I have change all y code and everything works fine now and I will stick with PDO for the rest of the book.

But even if I wanted to write it using mysqli, does mysqli not use prepared statements ? What does it use instead ? I understand that here I just created query strings and checked if they exit, but what would I have to do to execute them in the database ?

MySQLi does have prepared statements. Keep in mind that PDO is more of wrapper that provides a standard “interface” to lots of different types of databases, including MySQL. The one nice thing about PDO’s implementation of prepared statements is that you can create named placeholders in your query, such as:

SELECT * FROM users WHERE fname = :first_name AND lname = :last_name 

Then PDO does a kind of “search-and-replace” with an array you pass to it that would look like this:

$data = array("first_name" => "Larry", "last_name" => "Smith");

With MySQLi, the placeholders are simply questions marks ? which are replaced with the data in the order it was passed.

2 Likes

The same as you did for the first one. See the difference:

$sql = "SELECT id FROM joke WHERE authorid = '$id'";
    if (!mysqli_query($link, $sql))    

That second line, with mysqli_query, is the bit that actually executes the query, and checks to see whether it returns false or a results object.

1 Like

Mysqli does support prepared statements. However, until recently, its use was a real pain in the back. Besides. hordes of unsuspecting PHP users gladly keep using mysqli as a mere replacement for the old mysql ext, keeping the approach the same. this is why mysqli prepared statements are less known and even less used.

With recent PHP versions they are pretty usable though, but PDO is still better. Not because it “provides a standard interface” like @OhMonty said, and not because these bloating named placeholders but because its interface is much more concise and because of its ability to return the query results in dozens of different formats.

2 Likes

Thank you everyone ! It is fun to see that people respond quickly and are engaged to help a beginner like me. I will continue my learning now!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.