Need help with redirect... Will not display mysqli_fetch_assoc ?

[RandomNsanity]

Props to PHP Guru Pyscho who wrote/helped me with most of this script... it's pulling a link from a database that will redirect the user based on how many time's its been visited already. Almost done but I can't get the redirect to work at all....will using header("location: $link");  not work with a mysqli_fetch_assoc or something?  Each time it says page not found and it has "Array" on the end of the link address.... like it's seeing it as an array.

 

<?php 
//Connect to DB
$con = mysqli_connect('database', 'username', 'password', 'random_link);
if (!$con) { die('Could not connect: ' . mysql_error()); } 
//Get the first link with the lowest count and lowest ID
$query = "SELECT id, link FROM links_table ORDER BY count ASC, id ASC LIMIT 1";
$result = mysqli_query($con, $query);
$link = mysqli_fetch_assoc($result);
//Update the count for the selected link
$query = "UPDATE links_table SET count = count + 1 WHERE id = {$link['id']}";
$result = mysqli_query($con, $query);
//Redirect the user
header("Location: $link");
exit;
?>

Many thanks to any help!

 

[RandomNsanity]

Actually thinking about it more I think I need to select the data to display from the array... but how's the best way to do that? Am I pointed in the right direction?

[RandomNsanity]

Hell yeah that's what it was. Revised code that WORKS!

 

<?php 
//Connect to DB
$con = mysqli_connect('database', 'username', 'password', 'random_link');
if (!$con) { die('Could not connect: ' . mysql_error()); } 
//Get the first link with the lowest count and lowest ID
$query = "SELECT id, link FROM links_table ORDER BY count ASC, id ASC LIMIT 1";
$result = mysqli_query($con, $query);
$link = mysqli_fetch_assoc($result);
//Update the count for the selected link
$query = "UPDATE links_table SET count = count + 1 WHERE id = {$link['id']}";
$result = mysqli_query($con, $query);
//Redirect the user
header("Location: $link[link]");
exit;
?>

Thank you again Psycho... you made it much easier and I learned something  

[Jacques1]

Some parts of the code don't make a lot of sense, though.

 

First of all, you're mixing MySQLi with the old mysql_* functions. Those are two entirely different extensions which cannot be used together. I really wonder why everybody tries this.

 

This die(mysql_error()) stuff itself is a particularly bad anti-pattern which somehow gets copied and pasted around without anybody ever thinking about it. Do you realize that this will print the exact MySQL error message on the screen of your users? Why would you do that? The internal settings of your database are really none of your users' business. Handing them out will help attackers and irritate legitimate users. I don't know about you, but if I see something like “Could not connect to ...” with some cryptic data on a website, I get the feeling those guys don't really have their system under control.

 

Last but not least, you really need to start using prepared statements to securely pass PHP values to queries. Security is one of the core goals of MySQLi. If you don't use it at all, well, why did you even switch to MySQLi?

 

A sanitzied version of the code would look something like this:

<?php

// turn on exceptions in MySQLi driver so that you don't have to manually check every query for errors
$database_driver = new mysqli_driver();
$database_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

// connect to the database
$database = new mysqli('localhost', 'YOUR_USER', 'YOUR_PASSWORD', 'YOUR_DB');

// set the character encoding of the connection
$database->set_charset('utf8');

$link_query = $database->query('
    SELECT
        id
        , link
    FROM
        links_table
    ORDER BY
        count ASC   -- not exactly the best name
        , id ASC
    LIMIT
        1
');
$link = $link_query->fetch_assoc();

// update the access count with a prepared statement
$update_count_stmt = $database->prepare('
    UPDATE
        links_table
    SET
        count = count + 1
    WHERE
        id = ?
');
$update_count_stmt->bind_param('i', $link['id']);
$update_count_stmt->execute();

// redirect the user
header('Location: ' . $link['link']);
exit;

// omit closing PHP tag, because it's not needed and may introduce unwanted whitespace