php mysql assigning variables to column data

Question

I'm trying to assign MySQL column data to php variables so I can then send via email. It runs a query looking for a matching phone number in database and then returns the row/column data. Some of the variables are being brought in via a text message to this php script in case you're wondering why they are not in the mysql stuff. I'm getting an error at the while statement when I run in browser but I have a feeling my SELECT statement isn't right. Thanks!

<?php
session_start();

//$to_number_back = $_GET['to_number'];
$to_number_back = "+15551212";
$dcsrep = array();
$name = array();
$date = array();
$amount = array();
$digits = array();
$details = array();


//include_once("scripts/connect_to_mysql.php");
$servername = "****";
$username = "****";
$password = "****";
$dbname = "****";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$stmt = $conn->prepare('SELECT dcsrep, name, date, amount, digits, details     FROM ***uth WHERE to_number = ?');

$stmt->bind_param('s', $to_number_back);

$result = $stmt->execute();
$stmt->store_result();

while($row = $result->fetch_assoc()) {

$dcsrep[] = $row['dcsrep'];
$name[] = $row['name'];
$date[] = $row['date'];
$amount[] = $row['amount'];
$digits[] = $row['digits'];
$details[] = $row['details'];
}

if($stmt->num_rows > 0){
echo "Records received";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}


$conn->close();


$from_number = $_GET['from_number'];
$message = $_GET['message'];

    $to = "***@global.net";
    $from = "admin@d***.com";

    $subject = " Payment Authorization";

    //// start email body ////

    $message1 = "


From: $from_number

To: $to_number


Message:

$dcsrep
$name
$date
$amount
$digits
$details


$message



";

    //// Set das headers eh ////

    $headers = 'MIME-Version: 1.0' . "rn";

    $headers .= "Content-type: textrn";

    $headers .= "From: $fromrn";

    /// Okay, you send now!

    mail($to, $subject, $message1, $headers, '-f admin**ect.com');

echo "it worked";

?>

What is mysqli_query_fetch_assoc()? Is this your custom function? There is mysqli_query() and mysqli_fetch_assoc(). — Sean
– Sean, Commented Nov 30, 2015 at 21:12
Also, why are you doing while($row =mysqli_query_fetch_assoc($sql)) and then doing if ($conn->query($sql) === TRUE)? Why are you doing both a procedural style query in a loop, and then an object oriented query in an if? — Sean
– Sean, Commented Nov 30, 2015 at 21:14

Ohgodwhy · Accepted Answer · 2015-11-30 21:42:30Z

3

You've got a hodgeposh of mysql, mysqli, procedural and object oriented functions you're using here, you need to standardize.

First, let's change $conn to instantiate an instance of a mysqli connection and return an object.

$conn = new mysqli($servername, $username, $password, $dbname);

Then do your checks:

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

Now, you're going to be accepting user input data, so don't directly inject that into the string, instead take advantage of prepared statements. Furthermore, your number is actually a string and not an integer. However, our prepared statement and bind_param calls will handle that

$stmt = $conn->prepare('select dcsrep, name, date, amount, digits, details from ****uth where to_number = ?');

Now the ? is a placeholder, let's bind our data safely to it.

$stmt->bind_param('s', $to_number_back);
                   ^--- treat it as a string

Great. Now it's safely sanitized. Let's execute the statement now and walk over our returned results.

$result = $stmt->execute();

while($row = $result->fetch_assoc()){
    $dcsrep[] = $row['dcsrep'];
    $name[] = $row['name'];
    $date[] = $row['date'];
    $amount[] = $row['amount'];
    $digits[] = $row['digits'];
    $details[] = $row['details'];
}

Now you can proceed as you intend.

Edit

You should also not worry about $conn->query($sql) === TRUE. Instead, you should store the request and then evaluate the number of rows returned.

You will need to store the result directly after calling ->execute();

$result = $stmt->execute();
$stmt->store_result();

Now you can check the num rows instead of if the query was true.

if($stmt->num_rows > 0){
    //do your stuff, query found results.
} else {
    //die out
}

edited Nov 30, 2015 at 21:42

answered Nov 30, 2015 at 21:16

Ohgodwhy

50.9k11 gold badges86 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TReb Over a year ago

Ok, I'm getting the error Fatal error: Call to undefined method mysqli_stmt::fetch_assoc() after making the changes you show. I apologize that my script is cluttered. It's a combination of different things I've found.

Ohgodwhy Over a year ago

@TReb Sorry about that. I forgot to alias the execute() call to a variable. For clarity, I have aliased $result to the be the ... well result of $stmt->execute(). Now $result will have the collection on it, which you can call ->fetch_assoc(). Have a look at my edit above if you need more examples.

TReb Over a year ago

I edited my original post above with your code. I'm now getting the error Fatal error: Call to a member function fetch_assoc() on a non-object in... I appreciate your help!

Collectives™ on Stack Overflow

php mysql assigning variables to column data

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related