I have question about best practice of PHP Mysql connection. For now i have a database with thousand of rows. This row contains for example description of product. But in row i don't have a name but ID of NAME, i don't have a color but ID of Color etc. I`m doing that way for cause when i must hange name, or color - then i'm only change name in table COLORS and ID of color stays the same. Is that way OK?
I'm asking because sometimes i have a problem with multiple connections to mysqli. Below i`m showing my way of work:
// connection
function getConnected($host,$user,$pass,$db) {
$mysqli = new mysqli($host, $user, $pass, $db);
$mysqli->set_charset("utf8");
if($mysqli->connect_error)
die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());
return $mysqli;
}
function msq(){
return getConnected('127.0.0.1','root','','database');
}
// functions
function getcolor($temp){
$db2=msq();
if($result = $db2->query("SELECT * FROM linkcolor WHERE idcolor='$temp' ")){
if($result->num_rows > 0) {
$res = $result->fetch_assoc();
return $res['name'];
}else{
return 'none';
}
}else{
return 'none';
}
}
function getname($temp){
$db2=msq();
if($result = $db2->query("SELECT * FROM linkname WHERE idname='$temp' ")){
if($result->num_rows > 0) {
$res = $result->fetch_assoc();
return $res['name'];
}else{
return 'none';
}
}else{
return 'none';
}
}
// query
if($quer = $db->query("SELECT * FROM products")){
if($quer->num_rows > 0) {
while($qr = $quer->fetch_assoc()){
echo getcolor($qr['idcolor']);
echo getname($qr['idname']);
}
}else{
}
}else{
}
Is it a good way? Or better is to get data from mysql to array and then process them in function when i'm fetching my query?
It is a bad praxis to make a connection for a single query, so multiple connections to the same database. Typically you make a single connection to one database at the start of your script and close it at the end. You can always use $GLOBALS to access a mysqli link you created or create a static class for that purpose:
static class DB {
static protected $link = null;
static public function getLink() {
if (self::$link === null) {
self::$link = msq();
}
return self::$link;
}
}
then you can access your connection with
$db2 = DB::getLink();