same exact code works on one page, not the other, am I blind?

[moose-en-a-gant]

Haha the title

 

So I created a simple username database comparison to see if a username is already registered, this works in one website but not in the one I'm working on now. They have different databases and the logins work, I have inserted data into the second website which I'm having problems with.

 

So this is website one which works

 

I am told not to do multiple logins, yes I agree, however when I tried to only do one at the moment it does not work, not sure why

$servername = " ";
$username = " ";
$password = " ";
$dbname = " ";

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

if($_SERVER['REQUEST_METHOD']=='POST'){

 $errors = array();
 
function test() {
$test = test_input($_POST['userrname']);
$servername = " ";
$username = " ";
$password = " ";
$dbname = " ";
$link = new mysqli("$servername", "$username", "$password", "$dbname");
$stmt = $link->prepare('SELECT DISTINCT username FROM User where username=?');
$stmt->bind_param('s',$test);

if($stmt->execute())
{
    // This variable ($repco_from_db) will hold the result from the database.
    $stmt->bind_result($username_from_db);
    if($stmt->fetch())
    {
    $test = $username_from_db;
    return $test;
   }
   }
}
$value = test();
 
if (empty($_POST['userrname'])) {
     $errors['username'] = "A username is required";
   } else if(!empty($value)) {
   $errors['username']="Username already exists";
}else {
     $userrname = test_input($_POST['userrname']);
   }

Here is the second website

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

if($_SERVER['REQUEST_METHOD']=='POST'){

$errors = array();

function test() {
$test = test_input($_POST['userrname']);
$servername = " ";
$username = " ";
$password = " ";
$dbname = " ";
$link = new mysqli("$servername", "$username", "$password", "$dbname");
$stmt = $link->prepare('SELECT DISTINCT username FROM Users where username=?');
$stmt->bind_param('s',$test);
if($stmt->execute())
{
    // This variable ($repco_from_db) will hold the result from the database.
    $stmt->bind_result($username_from_db);
    if($stmt->fetch())
    {
    $test = $username_from_db;
    return $test;
    $link->close();
   }
   }
}
$value = test();

if (empty($_POST['userrname'])) {
     $errors['userrname'] = "A username is required".'<br>';
     $errors['userrname-n'] = "*";
   } else if(!empty($value)) {
   $errors['userrname']="Username already exists".'<br>';
   $errors['userrname-n'] = "*";
}else {
     $userrname = test_input($_POST['userrname']);
   }

  Just to clarify the problem, on website two when I first visit it, the page loads, if I don't fill in anything and press submit, the page is blank, no database inserts, I look around and the problem is the $stmt->execute, when I remove this, the page redirects, data is inserted however fields being blank of course in the database.

There is an insert statement farther down not shown here which utilizes the server login information above post.

So I'm not sure what is going on, I do a variable dump on $stmt->execute() (not the whole thing just $stmt) and the array output says object, but that's pretty much it, nothing really useful (to me anyway).

So I'm curious why the blank page.

I realize too the logic of the code, if there are no entries, and no username to check, is an "emtpy value recorded" which even with that "mystery" <- dumb, it still works because the first website uses this and it works.

 

I even add an else statement that sets $test as null which I read is equivalent to empty, but the whole page stops at $stmt->execute, so why is that?

 

I imagine it has to do with the server connection not working, if I remove $stmt->execute, the entire page does its thing, partially complete data is inserted. So what do you guys think is the problem?

[moose-en-a-gant]

Yeah I'm aware that I have some uhh... what's the word... lingering? code like the repco which is one of my first website projects that someone outlined for me the basics of a php form submit page on coding forums

[scootstah]

White page of death is usually a fatal PHP error. Check your Apache error.log.

[maxxd]

And make sure you have error reporting turned on during development:

error_reporting(-1);
ini_set('display_errors',true);

[moose-en-a-gant]

 

And make sure you have error reporting turned on during development:

error_reporting(-1);
ini_set('display_errors',true);

 

Oh man that was perfect, I had these which didn't show any errors

 

mysqli_report(MYSQLI_REPORT_OFF);

error_reporting(E_ALL);

error_reporting(-1);

 

Yes the first one is off because the error which was shown by the ini_set was a lack of index which I asked about in the past, I still haven't solved the problem

 

The solution I have found on stack overflow was to turn off mysqli_report which indeed works now, no white page. I have an index or key set. I don't know what to do about the error of no index used.

[scootstah]

I don't know what to do about the error of no index used.

Have you tried adding an index? lol

[moose-en-a-gant]

I don't understand what that means

 

I have id and it is autoincremented

 

then under the table structure it says INDEXES: and id is shown Type BTREE Unique No

[scootstah]

You need to make the id column a primary key. That might be what it's complaining about.

[Barand]

I'm convinced the "no index used" error is a mysql bug.

CREATE TABLE `tablea` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`position` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

then

mysqli_report(MYSQLI_REPORT_ALL);

$db->query("SELECT id, name FROM tablea");

gives

 

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'No index used in query/prepared statement SELECT id, name FROM tablea'

[scootstah]

Huh, that's odd. I've never run into that before.

[tryingtolearn]

It could be since mysql throws tons of warnings, setting the report to report all but not catching those warnings changes them to fatal errors

Put it in a try catch block and it should no longer throw a fatal error.

Just a thought.

[moose-en-a-gant]

 

It could be since mysql throws tons of warnings, setting the report to report all but not catching those warnings changes them to fatal errors

Put it in a try catch block and it should no longer throw a fatal error.

Just a thought.

 

The ini_set helped, that seems to show errors, I mean if it doesn't work I know something is wrong haha then turn the errors on, it seems though that MySqli All is my primary problem.

 

Thanks for your input

[tryingtolearn]

Good if that works 

Setting it to all - I wouldnt call that a problem though

If used, it could be beneficial

Catch those exceptions and set up some custom error handling and you can be notified of potential problems

 

it can really help in the long run. Just my 2 cents...

[Barand]

It could be since mysql throws tons of warnings, setting the report to report all but not catching those warnings changes them to fatal errors

Put it in a try catch block and it should no longer throw a fatal error.

Just a thought.

It doesn't throw the fatal error but it does still give a no index used.

mysqli_report(MYSQLI_REPORT_ALL);

try {
    $res = $db->query("SELECT id, name FROM tablea ORDER BY id");
    if ($res) {
        while (list($i, $n) = $res->fetch_row()) {
            echo "$i, $n<br>";
        }
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

//RESULT

Error: No index used in query/prepared statement SELECT id, name FROM tablea ORDER BY id

Edited February 5, 2015 by Barand

[tryingtolearn]

interesting 

So something prevents the index from working automatically the way it should.

I had to actually add USE INDEX and set a WHERE clause to get it to go through.

 

(assuming the id index is named PRIMARY)

mysqli_report(MYSQLI_REPORT_ALL);

try {
    $res = $db->query("SELECT id, name FROM tablea USE INDEX(PRIMARY) WHERE id > 0");
    if ($res) {
        while (list($i, $n) = $res->fetch_row()) {
            echo "$i, $n<br>";
        }
    }

} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}