How to use a string from mysqli_result in an insert query?

Question

I've been searching and trying different solutions for about 3 hours, and now I'm caving and posting this question.

I have the following code;

<?php
include 'db.php';


$trackcd = $_GET['trackCD'];
$trackTitle = $_GET['trackTITLE'];
$duration = $_GET['secDuration'];

$artIDD = mysqli_query($conn, "SELECT artID FROM cd WHERE cdID=$trackcd");
$results = mysqli_query($conn, "SELECT artID, artName FROM artist WHERE artID=$artIDD");
$row = mysqli_fetch_assoc($results);

$sql = "INSERT INTO tracks (trackID, artID, artistName, trackCD, trackTITLE, secDuration) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('iisssi', $trackID, $artIDD, $row["artName"], $trackcd, $trackTitle, $duration);
$result = $stmt->execute();
if(!$result) echo "Failed";
//header('Location: tracks.php');
?>

but on line 10; $results = mysqli_query($conn, "SELECT artID, artName FROM artist WHERE artID=$artIDD");

I am getting the following error message;

Catchable fatal error: Object of class mysqli_result could not be converted to string in D:\xampp\htdocs\dashboard\inserttrack.php on line 10.

These queries each use a primary key, and only return one result, which I need for the INSERT query later. Most of the previous questions I have looked at suggest creating a loop, but I don't know how this will help me as I only get one result which I want to use later and does not need printing out.

The use of mysqli_query and $row = mysqli_fetch_assoc($results); are things I have picked up from similar questions, but they have not solved my problem.

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, May 02 '17 at 15:36
Thanks for the reminder. I will get right on that once I get the basics working. — AmberL0uise, May 02 '17 at 15:39
$artIDD = mysqli_query($conn, "SELECT artID FROM cd WHERE cdID=$trackcd"); — AmberL0uise, May 02 '17 at 15:39

score 0 · Answer 1 · answered Apr 05 '21 at 16:35

Your second attempts show us to do it properly. Just bind the variable you get from fetch_assoc().

This code should look like this:

$stmt = $conn->prepare('SELECT artID FROM cd WHERE cdID=?');
$stmt->bind_param('s', $trackcd);
$stmt->execute();
$results = $stmt->get_result();
$row = $results->fetch_assoc();
$artIDD = $row['artID'] ?? null;

$stmt = $conn->prepare('SELECT artID, artName FROM artist WHERE artID=?');
$stmt->bind_param('s', $artIDD);
$stmt->execute();
$results = $stmt->get_result();
$row = $results->fetch_assoc();

$sql = "INSERT INTO tracks (trackID, artID, artistName, trackCD, trackTITLE, secDuration) VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('iisssi', $trackID, $artIDD, $row["artName"], $trackcd, $trackTitle, $duration);
$result = $stmt->execute();

Of course, it would be much better if you did this all in one query, using joins or subqueries.

score -1 · Accepted Answer · answered May 02 '17 at 15:42

-1

You aren't getting the results of your first query to pass to your second query:

$query = mysqli_query($conn, "SELECT artID FROM cd WHERE cdID=$trackcd");//vulnerable 
$art_id_row = mysqli_fetch_assoc($query);
$art_id = $art_id_row['artID']

answered May 02 '17 at 15:42

Kisaragi

2,198
3
16
28

How to use a string from mysqli_result in an insert query?

2 Answers2