Using MySQLi to connect

Codes here !

Moderators: gesf, Michalio

Post Reply
KramsonKrate

I've just updated to PHP 5.6, (don't ask) and several things are depreciated from 5.3 which I had been using. I know this is simple, but I suppose I want to think it through out loud, and request suggestions for making it better.

My old code looked something like this:

Code: Select all

 <?php 
	 	 
		 include('req.php');
		 include('bank.php');
		 
	 $query="SELECT * FROM xx WHERE xx IN ('xx')";
		
		  $resource=mysql_query($query,$link);
		  echo "
		<table id=\"space\" class=\"sortable\" align=\"center\" border=\"0\" width=\"95%\">
		<tr>
		<td><b>Column 1</b></td><td><b>Column 2</b></td><td><b>Column 3</b></td><td><b>Column 4</b></td><td><b>Column 5</b></td><td><b>Column 6</b></td><td><b>Column 3</b></td></tr> ";
while($result=mysql_fetch_array($resource))
	{ 
	echo "<tr><td>".$result[1]."  </td><td>".$result[8]."</td><td>".$result[15]."</td><td>".$result[21]."</td><td>".$result[17]."</td><td>".$result[16]."</td><td><a href='/pages/things.php?result=$result[24]'>Link</a></td></tr>";
	} echo "</table>";
	 ?></p>
Where bank.php looks like this:

Code: Select all

<?php
$link = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);
?>
and req.php looks something like this:

Code: Select all

<?php
$dbhost = 'db.server.com';
$dbuser = 'xx';
$dbpass = 'xx';
$dbname = 'xx';
?>
I think the right answer is to change bank.php to this:

Code: Select all

<?php
$link = mysqli_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysqli_database($dbname);
?>
But I'm not sure how to phrase the query. Can anyone help here? Many thanks in advance!
Top
KramsonKrate

^ bump

Can anyone please help with the above issue? Kind regards!
Top
hyper

You may not like this, but don't take it the wrong way.
I'm not sure how to phrase the query
Whatever SQL you have, will work with very few tweaks depending on how badly it was written in the first place.

I'm not sure about everyone else, but, I looked at your code and thought 'this code that you have shown us is very old and out of date - it's not something that will easily be fixed with a simple line change'.

The best answer that I can give is to learn current HTML, CSS, PHP and SQL / database. Your HTML looks to be version 3 with an attempt at using HTML 4 (the addition of CSS) although I'll concede you may have thought that you were using HTML 4 but were using transitional in much the same way you are now trying the use MySQLi.

The PHP that you have shown, to my knowledge will work in PHP 5.6, although not recommended and I haven't checked.

Long story or not, you need to ditch the newly(?) upgraded PHP 5.6 in favour of PHP 7 in any case.

For database access in PHP, you should use PDO in place of MySQL / MySQLi and use it in the correct way - by this I mean that you should be using prepared statements rather than replacing mysql_xxx for whatever else will work.

The changes introduced to HTML, PHP and SQL were done for a reason.
Top
KramsonKrate

I very much appreciate your reply - it confirms my suspicions. I built this application in 2005, and it has worked perfectly since then. I built it to learn basic LAMP stack concepts, and it was an awesome project for what it was.

My host has 5.6 by default in my service, so I need to move to a new host.

I will do as you say, and educate myself on these concepts so I can rebuild this application.

Thank you, and kindly.
Top
hyper

Most of what you already know still applies, I think CSS will be the biggest change for you.

HTML provides the structure of the document - write it as though you are writing for a screen reader
CSS supplies how it looks on different devices

The main mind shift will be changing how you present variables to a query - it's called preparing and seems quite daunting to begin with.

Don't believe everything that you read in tutorials as some (many) are rather rubbish, you can still learn from them though.

Although I don't think that this is a beginners tutorial, it looks as though you should be able to cope with it as you do have some knowledge of SQL to begin with, and will hopefully have a flying start.
Top
KramsonKrate

This is incredibly kind of you - and I will endeavor to do as you suggest. I do have strong experience with very basic SQL, and have done a good deal of css troubleshooting, but have never started from scratch with css.

I have two kids and a full-time+ job, but this application is precious to me, and I've wanted to update it with a responsive design for over a decade.

It's an application that displays books in my collection (8000+), and related details and statistics. I want to build out the SQL to handle views and objects, but haven't had the time. Instead of doing that with the time I had when I built it, I learned how to build a search engine from scratch, and that was an awesome learning experience that I will never regret.

But the vision is to be able to use a mobile device to check if I have a book/edition in my collection. The main interface in my home is intended to be a lovely, old-school card catalogue, which butterflys-open when you pull the center drawers to reveal a tablet stand which runs this application alone.

I will take this work to another thread here in this excellent forum, and post my progress. Kind regards.
Top
KramsonKrate

Actually - the main vision for this project originally was to use this use-case as a reason to build, fail, and learn! I learned a lot, but there are clearly lots of new things to learn, here, over a decade later.
Top
KramsonKrate

Small update: I've posted on my intention to overhaul this project here:
https://www.php-forum.com/phpforum/view ... =2&t=29783

Please let me know if you have ideas, or follow along if you're interested as I document my progress!
Top
yqqwe

i encounter the same mysqli connection problem, i found out the host name is not correct in my case , hope this can help you ,op :) :) Image
Top
Strider64

Hmm? Where to start?

My first recommendation would to be to use PDO instead of mysqli to access the MySQL Database Table(s). A good resource that I even still use on occasion is this -> https://phpdelusions.net/pdo

I would start small and setup a PHP Sandbox that you can test out your PHP and PDO. That what I do and once you get a firm grasp on PDO then transfer it over the Project. The process actually goes pretty fast.

As for the HTML/CSS the main thing to do is get a firm understanding on the basics and the rest will come over time. Get a good How-to-book (a current version) or a good online tutorial (be wary of outdated tutorials as there a lot floating around on then internet). Think of HTML as the foundation of a cake and the CSS as the icing. You have a good foundation (the HTML) the CSS can easily be added/edited later on. The HTML will even look basic, but that isn't your main concern as you want to get to the meat & potatoes (the PHP). One last thing try to keep your PHP and HTML/CSS separated as much as possible. I usually try to keep most of the PHP at the top and the HTML/CSS at the Bottom. Here's an example on what I am talking about -

Code: Select all

<?php
require_once 'assets/config/config.php';
require_once "vendor/autoload.php";

use PhotoTech\CMS;
use PhotoTech\Pagination;

/*
 * Using pagination in order to have a nice looking
 * website page.
 */

if (isset($_GET['page']) && !empty($_GET['page'])) {
    $current_page = urldecode($_GET['page']);
} else {
    $current_page = 1;
}

$per_page = 4; // Total number of records to be displayed:
$total_count = CMS::countAllPage('blog'); // Total Records in the db table:


/* Send the 3 variables to the Pagination class to be processed */
$pagination = new Pagination($current_page, $per_page, $total_count);


/* Grab the offset (page) location from using the offset method */
$offset = $pagination->offset();
//echo "<pre>" . print_r($offset, 1) . "</pre>";
//die();
/*
 * Grab the data from the CMS class method *static*
 * and put the data into an array variable.
 */
$cms = CMS::page($per_page, $offset, 'blog');


?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=yes, initial-scale=1.0">
    <title>The Photo Tech Guru Blog</title>
    <link rel="stylesheet" media="all" href="assets/css/styles.css">
</head>
<body class="site">

<header class="masthead">

</header>

<?php include_once "assets/includes/inc.nav.php"; ?>

<main class="content" class="main">
    <div class="container">
        <?php foreach ($cms as $record) { ?>
            <article class="cms">
                <img class="article_image"
                     src="<?php echo htmlspecialchars($record['image_path']); ?>" <?= getimagesize($record['image_path'])[3] ?>
                     alt="article image">
                <h2><?= $record['heading'] ?></h2>
                <span class="author_style">Created by <?= $record['author'] ?> on
                    <time datetime="<?= htmlspecialchars(CMS::styleTime($record['date_added'])) ?>"><?= htmlspecialchars(CMS::styleDate($record['date_added'])) ?></time>
                </span>
                <p><?= nl2br($record['content']) ?></p>
            </article>
        <?php } ?>
        <?php
        $url = 'blog.php';
        echo $pagination->new_page_links($url);
        ?>
    </div>
</main>

<div class="sidebar">

</div>

<footer class="colophon">
    <p>&copy; <?php echo date("Y") ?> The Photo Tech Guru</p>
</footer>

</body>
</html>


Yes, there is PHP intermix with the HTML; however, it is usually for display purposes and not the actual setup (configuration/execution).

Here's a GitHub Repository of a project that I'm currently working on https://github.com/Strider64/phototechguru and the actual website https://www.phototechguru.com/.

One last thing that project is screaming pagination (that's if you currently not doing it) and that is something to look into.

I will glad to help as I am sure others will as well.

HTH - John
Top
Post Reply

Return to “mySQL & php coding”