Tweaked MySql Connection - Now Select Query's Fail

Hi Guys'

Yes it me again ...

I've updated some of php pages to include an access control function which works great - super pleased about that! However, the code I've used seems to conflict with select queries I've previously written.

Can you tell me what causes the problem please if I post some examples below?

Previously my config.php file looked like this: -

<?php


	/* Access to the databse via link commands requires the $link statement
	in order for them to be successful, also the term "localhost" stops the $link
	failing in the event of a change in your IP Address.*/
	
	$link = mysqli_connect("localhost" , "user", "password", "DbName");


?>

Open in new window

Now it includes some access control functions and the config file looks like this:

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost";
$db_name = "DbName";
$db_user = "user";
$db_word = "password";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

Open in new window

The first config allowed the following select queries to run: -

<?php
	
	$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`,	`sales14D`, `supplierCompany`, `itemCost` FROM inventory";
	
	if ($result=mysqli_query($link, $query)) {
	
		while ($row = mysqli_fetch_array($result)) {
		
	?>
			<tr>
			<td><?php echo $row['partID'];?></td>
			<td><?php echo $row['partDescription'];?></td>
			<td><?php echo $row['qtyInStock'];?></td>
			<td><?php echo $row['sales42D'];?></td>
			<td><?php echo $row['sales14D'];?></td>
			<td><?php echo $row['supplierCompany'];?></td>
			<td><?php echo $row['itemCost'];?></td>
			</tr>
						
<?php
		}
	
	} else {
	
		echo "It failed!";
	
	}
?>

Open in new window

The 2nd config files causes the connection to fail ... what am I doing wrong?

Thanks J

RidgejpAsked: 2016-07-30

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 111

Ray PaseurCommented: 2016-07-30

Without a test case, we have to guess a little bit, but I think this is an educated guess.

PHP has two ways of using the MySQLi extension. One is procedural and one is object-oriented. Details in the man pages here (for the connect() or __construct() functions):
http://php.net/manual/en/function.mysqli-connect.php
http://php.net/manual/en/mysqli.construct.php

As written, the functions return different variables.
$link = mysqli_connect("localhost" , "user", "password", "DbName");
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

Since $link is not the same as $mysqli, the rest of the code cannot use $link or $mysqli interchangeably. My recommendation is to use the object-oriented notation with $mysqli, and dispense with the procedural notation entirely. The primary reason for this recommendation is that object-oriented programming is more susceptible to automated testing, and is more in line with modern design patterns. Procedural programming kind of went into the rubbish bin at the end of the 20th century. It's also easier to swap out the old, obsolete mysql statements for the object-oriented mysqli statements.

Details are available in this article, including tested-and-working examples.
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

It's also syntactically easier to use the object-oriented fetch method. No need to flop back and forth between PHP and HTML. Fewer fiddly punctuation marks means fewer parse errors, too. Greater programmatic flexibility since the rows are returned in the form of objects instead of arrays. Here's an easier and more efficient way to write the code (untested but probably correct in principle):

<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    trigger_error( /* FAILURE DIAGNOSTICS */, E_USER_ERROR);
}
while ($row = mysqli_fetch_object($result)) {
    $row = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $row;
}

Open in new window

RidgejpAuthor Commented: 2016-07-30

Hi Ray,

My query looks like this now, however, the pages fails to load at all ... did I do something wrong? Thanks for the links too more bedtime reading ... your section on access_control() was great!

</div>
		
		<table id="table" class="table table-striped">	
			<thead>
			<tr>
				
			
			<th class="success">partID</th>
			<th class="success">Description</th>
			<th class="success">stockQty</th>
			<th class="success">sales42D</th>
			<th class="success">sales14D</th>
			<th class="success">supplier</th>
			<th class="success">itemCost (£)</th>
			
			</tr>
			</thead>
			<tbody class="searchable">
			
<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    trigger_error( /* FAILURE DIAGNOSTICS */, E_USER_ERROR);
}
while ($row = mysqli_fetch_object($result)) {
    $row = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $row;
}
		</table>
</div>

Open in new window

LVL 111

Ray PaseurCommented: 2016-07-30

Sorry, my "cut and paste" mistake. I never use the procedural code, and sometimes forget to ferret it out! Try it like this (line 8, et seq):

<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    trigger_error( /* FAILURE DIAGNOSTICS */, E_USER_ERROR);
}
while ($row = $result->fetch_object()) {
    $tr = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $tr;
}

Open in new window

Also, you might want to include some kind of "failure diagnostics" instead of the comment. If the query ever fails, you'll want to be aware of the issue.

RidgejpAuthor Commented: 2016-07-30

Still no joy sorry ... although I've noticed that the opening table statement is no longer linked color-wise to the closing table statement. I'm using brackets to code my php.

Screenshot and code attached ...

<table id="table" class="table table-striped">	
			<thead>
			<tr>
				
			
			<th class="success">partID</th>
			<th class="success">Description</th>
			<th class="success">stockQty</th>
			<th class="success">sales42D</th>
			<th class="success">sales14D</th>
			<th class="success">supplier</th>
			<th class="success">itemCost (£)</th>
			
			</tr>
			</thead>
			<tbody class="searchable">
			
<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    trigger_error( /* FAILURE DIAGNOSTICS */, E_USER_ERROR);
}
while ($row = $result->fetch_object()) {
    $tr = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $tr;
}
		</table>

Open in new window

RidgejpAuthor Commented: 2016-07-30

Screenshot-2016-07-30-17.19.22.png

LVL 111

Ray PaseurCommented: 2016-07-30

Well, we are still looking at fragments, so we need to do some diagnostics. After the connection, please add var_dump($mysqli). Then add the diagnostics in place of the comment, /* FAILURE DIAGNOSTICS */. The general design for the diagnostics can be found in the article here:
https://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

In that article, look for the part about Create and Execute a SELECT Query. You can copy the diagnostic code from the MySQLi example where it says "IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR." Hopefully, after we add the diagnostics to the script, we will get a signal of what went wrong and we can fix that.

RidgejpAuthor Commented: 2016-08-01

Is this correct now Ray - struggling a little with it?

</div>
		
		<table id="table" class="table table-striped">	
			<thead>
			<tr>
				
			
			<th class="success">partID</th>
			<th class="success">Description</th>
			<th class="success">stockQty</th>
			<th class="success">sales42D</th>
			<th class="success">sales14D</th>
			<th class="success">supplier</th>
			<th class="success">itemCost (£)</th>
			
			</tr>
			</thead>
			<tbody class="searchable">
			
<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    $err
    = "QUERY FAIL: "
    . $sql
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}

var_dump($mysqli); 
                
while ($row = $result->fetch_object()) {
    $tr = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $tr;
}
		</table>
</div>

Open in new window

LVL 111

Ray PaseurCommented: 2016-08-01

Maybe a bit more like this (variable names have to be used consistently). How does it test out?

</div>
        
        <table id="table" class="table table-striped">  
            <thead>
            <tr>
                
            
            <th class="success">partID</th>
            <th class="success">Description</th>
            <th class="success">stockQty</th>
            <th class="success">sales42D</th>
            <th class="success">sales14D</th>
            <th class="success">supplier</th>
            <th class="success">itemCost (£)</th>
            
            </tr>
            </thead>
            <tbody class="searchable">
            
<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    $err
    = "QUERY FAIL: "
    . $query
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
                
while ($row = $result->fetch_object()) {
    $tr = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $tr;
}
        </table>
</div>

Open in new window

RidgejpAuthor Commented: 2016-08-01

All changed ... but still not loading page. J

</div>
        
        <table id="table" class="table table-striped">  
            <thead>
            <tr>
                
            
            <th class="success">partID</th>
            <th class="success">Description</th>
            <th class="success">stockQty</th>
            <th class="success">sales42D</th>
            <th class="success">sales14D</th>
            <th class="success">supplier</th>
            <th class="success">itemCost (£)</th>
            
            </tr>
            </thead>
            <tbody class="searchable">
            
<?php
    
$query = "select `partID`, `partDescription`, `qtyInStock`, `sales42D`, `sales14D`, `supplierCompany`, `itemCost` FROM inventory";
    
if (!$result = $mysqli->query($query)) {
    $err
    = "QUERY FAIL: "
    . $query
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    ;
    trigger_error($err, E_USER_ERROR);
}
                
while ($row = $result->fetch_object()) {
    $tr = <<<EOD
            <tr>
            <td> $row->partID </td>
            <td> $row->partDescription </td>
            <td> $row->qtyInStock </td>
            <td> $row->sales42D </td>
            <td> $row->sales14D </td>
            <td> $row->supplierCompany </td>
            <td> $row->itemCost </td>
            </tr>
EOD;
    echo $tr;
}
        </table>
</div>

Open in new window

LVL 111

Ray PaseurCommented: 2016-08-01

Parse error on line 50, maybe? It looks like you might need to close out PHP and go back to straight HTML there.

Are you using a framework or CMS in this application?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

RidgejpAuthor Commented: 2016-08-01

Can't believe I missed that ... pop that in now and see what happens. I'm building each page from scratch piece by piece to replicate what my MS_Access Db achieves for my business so pushing myself a little so I guess that means I'm using framework as I'm not writing it within a Content Management System if that's what you mean?

Report back shortly ... thanks for sticking with me on this one.
J

RidgejpAuthor Commented: 2016-08-01

Bingo! Query loads successfully thanks for all your help - I just need to research what everything you included means so that I can better understand how to mod the other queries.

Thanks again ... J

LVL 111

Ray PaseurCommented: 2016-08-01

Glad to help, and best of luck with it! ~Ray

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

MySQL Server

From novice to tech pro — start learning today.

System Programming

By:

Don Kiely

Programming C# 6: Advanced

Premium

Premium members can enroll in this course at no extra cost.
Learn More

99 lessons
System Programming

By:

Don Kiely

SQL Server Core 2016

Premium

Premium members can enroll in this course at no extra cost.
Learn More

81 lessons
Microsoft Applications

By:

Sandra Batakis

Certification
Certification: MOS Microsoft Office Specialist - Outlook 2013 Par…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

139 lessons
Acronis

By:

Sam Simon Nasser

System Imaging and Deploying, using Acronis True Image.

Premium

Premium members can enroll in this course at no extra cost.
Learn More

6 lessons
Microsoft Applications

By:

Patrick Loner

Certification
Certification: MOS Microsoft Office Specialist - Outlook 2016 Par…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

175 lessons
Microsoft Office

By:

Drew Fierst

Certification
Certification: MOS Microsoft Office Specialist - Access 2010 Ad…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

171 lessons
Databases

By:

David Fraas

Amazon Web Services Intermediate

Premium

Premium members can enroll in this course at no extra cost.
Learn More

46 lessons
Human Resources

By:

Angela Kegler

Thinking Critically

Premium

Premium members can enroll in this course at no extra cost.
Learn More

106 lessons
Business Management

By:

Martin VanDerSchouw

Executive Strategy and Management

Premium

Premium members can enroll in this course at no extra cost.
Learn More

69 lessons
Microsoft Applications

By:

Debbie Fierst

Microsoft Project 2013: Part 2

Premium

Premium members can enroll in this course at no extra cost.
Learn More

123 lessons