Error connecting to database

TonyR · February 27, 2019

Hi

I am struggling to connect to my database and was wondering if someone could please help me. I am not sure what I need to put on line 10 in this code

<?php

define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'tonyruttle');

define("PERPAGE", 10);

$mydb = (DB_HOST, DB_USER, DB_PASS, DB_NAME);


/***********************************************
**  SEARCH FOR MATCHING PRODUCTS 
************************************************/
$showResults = 0;
$search = '';
if (isset($_GET['srch'])) {
    $search = $_GET['srch'];
    $showResults = 1;
    $srch = array_filter(array_unique(explode(' ', trim($_GET['srch']))));
    array_walk($srch, function(&$v, $k) {                                       // add the +'s to the search tags
                         $v = '+'.$v;
                      });
                                
    $tagparam = join(' ', $srch);
    //
    // FINDTOTAL RECORDS IN SEARCH RESULTS                                      
    //
    $params[] = $tagparam;
    $res = $db->prepare("SELECT COUNT(*) as tot
                        FROM  television p 
                        WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE)
                        ");
    $res->execute( $params );
    $total = $res->fetchColumn();

    $page = $_GET['page'] ?? 1;
    
    
    $params[] = ($page-1)*PERPAGE;    // append parameters offset
    $params[] = PERPAGE;              // and number of records for limit clause
    //
    // GET A PAGEFUL OF RECORDS
    //
    $sql = "SELECT 
                   id
                 , title
                 , active
            FROM television p 
            WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE)
            ORDER BY TITLE
            LIMIT ?,? ";
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    
    if ($stmt->rowCount()==0) {
        $results = "<h3>No matching records</h3>";
    } else {
        $results = "<tr><th>Product Id</th><th>Title</th><th>Active</th><th colspan='2'>Action</th></tr>\n";
        foreach ($stmt as $rec) {
            $cls = $rec['active']==0 ? "class='inactive'" : '';
            $results .= "<tr $cls><td>{$rec['id']}</td><td>{$rec['title']}</td><td class='ca'>{$rec['active']}</td>
            <td class='ca'><a href='?action=edit&id={$rec['id']}'><img src='images/edit-icon.png' alt='edit'></a></td>
            </tr>\n";
        }
    }
}
?>
<div id='title'>Product List</div>
<form id='form1' ">
    <fieldset>
    <legend>Search titles and tags</legend>
        <input type='text' name='srch' id='srch' size='50' value='<?=$search?>' placeholder='Search for...' >
        <input type="hidden" name="page" id="page" value="1">
        <input type="hidden" name="action" value="search">
        <input type="submit" name="btnSub" value="Search">
    </fieldset>
    <div id='formfoot'></div>
</form>
<?php if ($showResults) { ?>
         <div class="paginate_panel">
             <?=page_selector($total, $page, PERPAGE)?>
         </div>
         <table border='1'>
             <?=$results?>
         </table>
<?php } ?>

NotionCommotion · February 27, 2019

You are using PDO in your later code which is good. You next need to create a PDO connection. The docs give an example which was copied below. Obviously, you want to rename $dbh to $db.

Note that if you simple searched "php pdo", the first result would be http://php.net/manual/en/book.pdo.php which doesn't give any examples. Instead of randomly going through each link (which isn't necessarily a waste of time), always check the __construct link first.

PS. Get rid of all your constant definitions for your database connection values. At first you might think it is a good idea, but having too many constants gets complicated fast. I like to locate a file called config.ini in a secure location (i.e. not in your public root directory!) and use parse_ini_file() to convert it into an array.

<?php
/* Connect to a MySQL database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

?>

Edited February 27, 2019 by NotionCommotion
Added parse_ini_file comment

TonyR · February 27, 2019

Thank you NotionCommotion for the reply. I have managed to connect to the database. I am now having a fatal error. I understand the error but just don't know how to fix it. The fulltext columns in my database are title, description and keywords.

Uncaught PDOException: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list on line 65

<?php                                                 

define("PERPAGE", 15);                                                // number of records on each page

/**************************************************************************************
* function to output page selection buttons
*                     
* @param int $total   total records
* @param int $page    current page number
* @return string      selection buttons html
*/
function page_selector($total, $page)
{
    if ($total==0) {
        return '';
    }
    $kPages = ceil($total/PERPAGE);
    $filler = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;';
    $lim1 = max(1, $page-2);
    $lim2 = min($kPages, $page+3);
    $p = $page==1 ? 1 : $page - 1;
    $n = $page== $kPages ? $kPages : $page + 1;;
    $out = "$kPages page" . ($kPages==1 ? '' : 's') . " &emsp;";
    if ($kPages==1) {
        return $out;
    }
    $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : '';
    if ($page > 4) {
        $out .= "<div class='pagipage' data-pn='1'>1</div> $filler";
    }
    elseif ($page==4) {
        $out .= "<div class='pagipage' data-pn='1'>1</div>";
    } 
    for ($i=$lim1; $i<=$lim2; $i++) {
        if ($page==$i)
            $out .= "<div class='pagicurrent'>$i</div>";
        else
            $out .= "<div class='pagipage' data-pn='$i'>$i</div>";
    }
    if ($page < $kPages-3) {
        $out .= "$filler <div class='pagipage' data-pn='$kPages'>$kPages</div>";
    }
    $out .= $page < $kPages ? "&ensp;<div class='pagipage' data-pn='$n'>Next</div>" : '';
    return $out;
}

/***********************************************
**  SEARCH FOR MATCHING TVS
************************************************/
$showResults = 0;
$search = '';
if (isset($_GET['tag'])) {
    $showResults = 1;
    $search = $_GET['tag'];
    $srch = array_unique(explode(' ', trim($_GET['tag'])));
    foreach ($srch as $t) {
        $repl[] = "<span class='hilite'>$t</span>";
        $placeholders[] = '?';
        $params[] = $t;
    }
    $params[] = count($srch);
    //
    // FINDTOTAL RECORDS IN SEARCH RESULTS                                      
    //
    $res = $db->prepare("SELECT COUNT(*) as tot
                        FROM  television p 
                        WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE)
                        ");
    $res->execute($params);
    $total = $res->fetchColumn();

    $page = $_GET['page'] ?? 1;
    
    
    $params[] = ($page-1)*PERPAGE;    // append parameters offset
    $params[] = PERPAGE;              // and number of records for limit clause
    //
    // GET A PAGEFUL OF RECORDS
    //
   $sql = "SELECT 
                   id
                 , title
                 , keywords
            FROM television p 
            WHERE MATCH(title,keywords) AGAINST(? IN BOOLEAN MODE)
            ORDER BY TITLE
            LIMIT ?,? ";
    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    
    if ($stmt->rowCount()==0) {
        $results = "<h3>No matching records</h3>";
    } else {
        $results = "<tr><th>Product Id</th><th>Title</th><th>Tags</th><th>Edit</th></tr>\n";
        foreach ($stmt as $rec) {
            $alltags = str_replace($srch, $repl, $rec['keywords']);
            $results .= "<tr><td>{$rec['id']}</td><td>{$rec['title']}</td><td>$alltags</td>
            <td><a href='?action=edit&id={$rec['id']}'><img src='edit-icon.png' alt='edit'></a></td>
            </tr>\n";
        }
    }
}
?>
<div id='title'>Television Search</div>
<form id='form1'>
    <fieldset>
    <legend>Search for tags</legend>
        <input type='text' name='tag' size='50' value='<?=$search?>' placeholder='Search for...' >
        <input type="hidden" name="page" id="page" value="1">
        <input type="hidden" name="action" id="page" value="search">
        <input type="submit" name="btnSub" value="Search">
    </fieldset>
    <div id='formfoot'></div>
</form>
<?php if ($showResults) { ?>
         <div class="paginate_panel">
             <?=page_selector($total, $page)?>
         </div>
         <table border='1'>
             <?=$results?>
         </table>
<?php } ?>

NotionCommotion · February 27, 2019

What have you tried?

SHOW INDEX FROM television; 
ALTER TABLE television ADD FULLTEXT(title,keywords);

TonyR · February 27, 2019

When I do SHOW INDEX FROM television it comes back with

PRIMARY id
FT title
FT description
FT keywords

NotionCommotion · February 27, 2019

I don't have too much experience with fulltext indexes. It isn't a PHP issue anymore but a SQL issue, and as such, I would recommend eliminating variables and running your query directly from the command line or through some SQL client if you wish. Maybe try the following?

SELECT COUNT(*) as tot
FROM  television p 
WHERE MATCH(title,description,keywords) AGAINST(? IN BOOLEAN MODE);

TonyR · February 27, 2019

I will definitely do what you recommended and remove some variables and run the query directly.

Also I am interested in you saying that this is a SQL error. Does that mean the PHP code has no errors? I have been struggling with this code for around a week now so I'll be very pleased to know the code is clean.

TonyR · February 27, 2019

I managed to get rid of that error. For anyone else who gets this error basically the MATCH clause should be in the same order as the fulltext index and include all the fulltext indexes.

NotionCommotion · February 28, 2019

Not saying necessarily that your PHP doesn't have errors, but that error in particular wasn't a PHP error by a SQL error. Yes, I thought but wasn't positive that order and including all the fulltext indexes was required.

Sign In

Error connecting to database

Recommended Posts

TonyR

Link to comment

Share on other sites

NotionCommotion

Link to comment

Share on other sites

TonyR

Link to comment

Share on other sites

NotionCommotion

Link to comment

Share on other sites

TonyR

Link to comment

Share on other sites

NotionCommotion

Link to comment

Share on other sites

TonyR

Link to comment

Share on other sites

TonyR

Link to comment

Share on other sites

NotionCommotion

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information