Solving the return value of an SQL Query in an Associative Array

Question

Once again I am at the mercy of your knowledge and hope you can help.

Actual question is the bold italics, however you won't be able to help without reading the information that I've given.

Background to Question - I'm creating a photography website (for my mum) using HTML, CSS, MySQL and PHP. I'm in the process of working on the database, specifically on allowing my mum to insert images into the database using this form (https://i.stack.imgur.com/cr0kC.png). She has no idea how to code, therefore I need to make it easy for her.

Database Background (what you need to know) - I've got an image_tbl and album_tbl. The album_tbl is shown here - https://i.stack.imgur.com/jRMHv.png - with each album having an ID and Name (forget the 'hidden'). The image_tbl is shown here - https://i.stack.imgur.com/Et5Y9.png - with the important part (for this question) being the albumName.

Aim - I've managed to populate the 'Insert a New Image' form with the albums from album_tbl (picture shows 'Exploration'). I want her to be able to click the AlbumName (so she knows what album to add to), yet I want the image she inserts to receive the albumID in the database. Here's a Pastebin of my code thus far.

http://pastebin.com/6v8kvbGH = The HTML Form, for helping me be aware of the 1st Form in the code...

http://pastebin.com/4X6abTey = PHP/MySQL Code. Here we have me calling the inputs in the form and using them in 2 SQL Queries. The first Query is aiming to get the albumID of the albumName that was entered, and this is where it goes wrong. The commented out statements (using //) are me error-checking, and albumName is passed on from the form. However, the number of rows returned from the 1st SQL Statement is 0, when it should be 1. This is where I need help as clearly something's wrong with my assoc array ...

2nd Aim - Once the 1st SQL Query is working, the 2nd SQL Query is hopefully going to input the required variables into image_tbl including the albumID I hopefully just got from the 1st SQL Query.

I hope this is all that's required, as far as I'm aware the people who understand this should be able to help with what I've given. Thanks very much in advance!

Jake

Someone asked me to paste the code - HTML Form:

    <h2>Insert a new image</h2><br>

    <form action="imagesInsert.php" method="POST" enctype="multipart/form-data">

        Name of Image: <input type="text" name="name" /><br>

        Date: <input type="text" name="dateTime" /><br>

        Caption: <input type="text" name="caption" /><br>

        Comment: <textarea type="text" name="comment" cols="40" rows="4"></textarea><br>

        Slideshow: <input type="text" name="slideshow" /><br>

        Choose an Album to place it in: 

            <?php
            mysql_connect('localhost', 'root', '');
            mysql_select_db('admin_db');

            $sql = "SELECT albumName FROM album_tbl WHERE hidden = false";
            $result = mysql_query($sql); ?>

            <select name='albumName'>; <?php
            while ($row = mysql_fetch_array($result)) {
                echo "<option value='" . $row['albumName'] . "'->" . $row['albumName'] . "</option>";
            }
            ?> </select>


        <input type="submit" name="submit"/><br>
    </form>



    <h2>Hide the Image</h2><br>

    <form action="imagesHidden.php" method="POST" enctype="multipart/form-data">

        Title: 

            <?php
            mysql_connect('localhost', 'root', '');
            mysql_select_db('admin_db');

            $sql = "SELECT name FROM image_tbl WHERE hidden = false";
            $result = mysql_query($sql);

            echo "<select name='name'>";
            while ($row = mysql_fetch_array($result)) {
                echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
            }
            echo "</select>";
            ?>

            <input type="submit" value="Hide" name="submit">

    </form>



    <h2> Renew from Hidden Items </h2><br>

    <form action="imagesRestore.php" method="POST" enctype="multipart/form-data">

        Title: 

            <?php
            mysql_connect('localhost', 'root', '');
            mysql_select_db('admin_db');

            $sql = "SELECT name FROM image_tbl WHERE hidden = true";
            $result = mysql_query($sql);

            echo "<select name='name'>";
            while ($row = mysql_fetch_array($result)) {
                echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
            }
            echo "</select>";
            ?>

            <input type="submit" value="Renew / Un-Hide" name="submit">

    </form>


</body>

Inserting the image using PHP/MySQL:

    <?php

$username="root";
$password="";
$database="admin_db";
$servername="localhost";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) 
{
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully <br><hr>";

$name        = $_POST['name'];
$dateTime    = $_POST['dateTime'];
$caption     = $_POST['caption'];
$comment     = $_POST['comment'];
$slideshow   = $_POST['slideshow'];
$hidden      = false;
$albumName   = $_POST['albumName'];

// echo "album name is" . $albumName;

$sql = "SELECT albumID FROM album_tbl WHERE albumName = $albumName";
$albumID = $conn->query($sql);

// echo "Number of rows is " . $albumID->num_rows;

if ($albumID->num_rows > 0) {
    // output data of each row
    while($row = $albumID->fetch_assoc()) {
        echo "Album ID: " . $row["albumID"]. "<br>";
    }
} else {
    echo "0 results";
}

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

$new_comment = str_replace("'", "''", $comment);

$sql = "INSERT INTO `image_tbl`(`name`, `dateTime`, `caption`, `comment`, `slideshow`, `hidden`, `albumID`) VALUES ('$name', '$dateTime', '$caption', '$new_comment', '$slideshow', '$hidden', '$albumID')";

$result = $conn->query($sql);

if ($result)
{
    echo "Data has been inserted";
} 
else
{
    echo "Failed to insert";
}

$conn->close();
?>

Please post your relevant code here so it is preserved for future SO visitors. — Jay Blanchard, Feb 10 '16 at 20:58
The background is irrelevent. Everyone creating a web site that inserts into databases is doing it for the same reason -- end users don't work with databases directly. — Barmar, Feb 10 '16 at 21:04
That's very true, I guess I didn't look at it from a professional point of view. @Barmar — Jacob Ryde, Feb 10 '16 at 21:06
You need quotes around the `$albumname` in `$sql`, because it's a string. But it would be better if you put the album ID in the option value, instead of the name. — Barmar, Feb 10 '16 at 21:08
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 10 '16 at 21:11
Oops, just noticed that he's using mysql in the first script, only the second one uses mysqli. — Barmar, Feb 10 '16 at 21:12

Barmar · Answer 1 · 2016-02-10T22:22:25.940

2

This line:

$sql = "SELECT albumID FROM album_tbl WHERE albumName = $albumName";

should be:

$sql = "SELECT albumID FROM album_tbl WHERE albumName = '$albumName'";

since the album name is a string.

You should check for errors when you perform a query:

$albumID = $conn->query($sql) or die($conn->error);

You can't use $albumID in the INSERT query. Despite the name of the variable, it doesn't contain an album ID, it contains a mysqli_result object that represents the entire resultset of the query -- you can only use it with methods like num_rows and fetch_assoc() to extract information from the resultset.

What you can do is use a SELECT statement as the source of data in an UPDATE:

$stmt = $conn->prepare("INSERT INTO `image_tbl`(`name`, `dateTime`, `caption`, `comment`, `slideshow`, `hidden`, `albumID`) 
    SELECT ?, ?, ?, ?, ?, ?, albumID
    FROM album_tbl
    WHERE albumName = ?";
$stmt->bind_param("sssssss", $name, $dateTime, $caption, $comment, $slideshow, $hidden, $albumName);
$stmt->execute();

Note that when you use a prepared query, you don't need to fix the quotes in $comment (which you should have done using $conn->real_escape_string($comment), not str_replace()).

Just to help you understand, this can also be done without a prepared query.

$sql = "INSERT INTO `image_tbl`(`name`, `dateTime`, `caption`, `comment`, `slideshow`, `hidden`, `albumID`) 
    SELECT '$name', '$dateTime', '$caption', '$new_comment', '$slideshow', '$hidden', albumID
    FROM album_tbl
    WHERE albumName = '$albumName'";

edited Feb 10 '16 at 22:22

answered Feb 10 '16 at 21:17

Barmar

741,623
53
500
612

Thanks very much, such a long time of trying to solve it and such a simple solution! The problem still remains in the 2nd Query, as it seems to die after the Assoc Array is completed and returns the ID. I looked into something called a 'Union' that connects two SQL Queries, otherwise I'm unsure how to follow the 1st with the 2nd query – Jacob Ryde Feb 10 '16 at 21:50
You can't use `$albumID` in the `INSERT` query. That's an object that represents the entire dataset of results. The actual album ID's are in `$row['albumID']`. – Barmar Feb 10 '16 at 21:55
I've updated the answer to show how to do the inserts. – Barmar Feb 10 '16 at 22:03
Ok, that last update of $stmt has shot way over my head. From what I understand, the code I currently have should work with the changes you first suggested. I'm receiving the albumID now from the assoc array, however I'm receiving a Syntax Error on the suggestion you made. I changed it to this in the SQL Insert Query ( '$hidden', '$row(['albumID'])'"; ) . It's creating a Syntax Error at the start of 'albumID... ' – Jacob Ryde Feb 10 '16 at 22:14
You need to read the PHP documentation on substituting variables into strings. http://php.net/manual/en/language.types.string.php Pay special attention to the section on complex variables. – Barmar Feb 10 '16 at 22:19
Also, if you're going to use `$row['albumID']`, you have to do the insert within the `while ($row = )` loop. Because when the loop is done, `$row = false`. – Barmar Feb 10 '16 at 22:19
I put that code into the answer specifically to force you to learn about prepared queries. It's a bad idea to substitute variables directly into queries, it's very error prone. – Barmar Feb 10 '16 at 22:20
I've shown how to do the INSERT+SELECT using variable substitution. – Barmar Feb 10 '16 at 22:22
You also need to do it in the loop so that you'll insert a row for every different albumID that matches the album name. – Barmar Feb 10 '16 at 22:23

jameshwart lopez · Answer 2 · 2016-02-10T22:20:54.200

First of all create a single database connection let say

db_connection.php

<?php
    $username="root";
    $password="1k9i2n8gjd";
    $database="admin_db";
    $servername="localhost";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $database);

    // Check connection
    if ($conn->connect_error){
        die("Connection failed: " . $conn->connect_error);
    }

    echo "Connected successfully <br><hr>";

Then in your form or any php file that needs database connection you can just include the db_connection.php so that you have one database connection.

Note: I have change the value of option to albumId so that you dont need to query or select based on albumName because you already have the albumID passed in imagesInsert.php via $_POST

<?php 
   require_once('db_connection.php');
   //include_once('db_connection.php');
?>
<html>
<head>
    <title>Admin Page | Alison Ryde's Photography</title>
    <link rel="stylesheet" type="text/css" href="../../css/style.css">
</head>
    <body>

        <h2>Insert a new image</h2><br>

        <form action="imagesInsert.php" method="POST" enctype="multipart/form-data">

            Name of Image: <input type="text" name="name" /><br>

            Date: <input type="text" name="dateTime" /><br>

            Caption: <input type="text" name="caption" /><br>

            Comment: <textarea type="text" name="comment" cols="40" rows="4"></textarea><br>

            Slideshow: <input type="text" name="slideshow" /><br>

            Choose an Album to place it in:

                <?php

                $sql = "SELECT albumName FROM album_tbl WHERE hidden = false";
                $result = $conn->query($sql);// mysql_query($sql); ?>

                <select name='albumName'>; <?php
                while ($row = $result->fetch_array()) {
                    echo "<option value='" . $row['albumID'] . "'->" . $row['albumName'] . "</option>";
                }
                ?> </select>


            <input type="submit" name="submit"/><br>
        </form>



        <h2>Hide the Image</h2><br>

        <form action="imagesHidden.php" method="POST" enctype="multipart/form-data">

            Title:

                <?php


                $sql = "SELECT name FROM image_tbl WHERE hidden = false";
                $result = $conn->query($sql);//mysql_query($sql);

                echo "<select name='name'>";
                while ($row = $result->fetch_array()) {
                    echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
                }
                echo "</select>";
                ?>

                <input type="submit" value="Hide" name="submit">

        </form>



        <h2> Renew from Hidden Items </h2><br>

        <form action="imagesRestore.php" method="POST" enctype="multipart/form-data">

            Title:

                <?php


                $sql = "SELECT name FROM image_tbl WHERE hidden = true";
                $result = $conn->query($sql);//mysql_query($sql);

                echo "<select name='name'>";
                while ($row = $result->fetch_array()) {
                    echo "<option value='" . $row['name'] . "'>" . $row['name'] . "</option>";
                }
                echo "</select>";
                ?>

                <input type="submit" value="Renew / Un-Hide" name="submit">

        </form>


    </body>
</html>

Then in your php code that inserts the data should be like this.

imagesInsert.php

<?php 
       require_once('db_connection.php');
       //include_once('db_connection.php');

$name        = $_POST['name'];
$dateTime    = $_POST['dateTime'];
$caption     = $_POST['caption'];
$comment     = $_POST['comment'];
$slideshow   = $_POST['slideshow'];
$hidden      = false;
$albumID     = $_POST['albumName'];


$new_comment = str_replace("'", "''", $comment);

$sql = "INSERT INTO `image_tbl`(`name`, `dateTime`, `caption`, `comment`, `slideshow`, `hidden`, `albumID`) VALUES ('$name', '$dateTime', '$caption', '$new_comment', '$slideshow', '$hidden', '$albumID')";

$result = $conn->query($sql);

if ($result)
{
    echo "Data has been inserted";
}
else
{
    echo "Failed to insert";
}

$conn->close();
?>

Another piece of advice is to use prepared statementif your query is build by users input to avoid sql injection

 <?php 
           require_once('db_connection.php');
           //include_once('db_connection.php');
    $name        = $_POST['name'];
    $dateTime    = $_POST['dateTime'];
    $caption     = $_POST['caption'];
    $comment     = $_POST['comment'];
    $slideshow   = $_POST['slideshow'];
    $hidden      = false;
    $albumID     = $_POST['albumName'];


    $new_comment = str_replace("'", "''", $comment);
 $sql = "INSERT INTO `image_tbl`(`name`, `dateTime`, `caption`, `comment`, `slideshow`, `hidden`, `albumID`) VALUES (?, ?, ?, ?, ?, ?, ?)";

$stmt = $conn->prepare($sql);
$stmt->bind_param("sssssss", $name, $dateTime, $caption,$new_comment,$slideshow,$hidden,$albumID);
$stmt->execute();

hope that helps :) good luck

I've used the Barmar's answer however I appreciate your help and will certainly follow your recommendation of the db_connection.php. - Jake — Jacob Ryde, Feb 10 '16 at 21:52
I dont think you should follow sql that is prone to sql injection.Though its very simple solution for your problem but that will break :) — jameshwart lopez, Feb 10 '16 at 22:07

Solving the return value of an SQL Query in an Associative Array

2 Answers2