how to get third table count with group concat

I have a three tables.
1) tbl_product base table //which stores the product information.

id int(11) unsigned
product_type_id int(11) unsigned
product_image   varchar(255)
title   varchar(255)
description text
price   float(8,2)
discount_price  float(8,2)
status  tinyint(1)
created_at  datetime
updated_at  datetime

2) tbl_product_review //which stores the reviews of every product.

id  int(10) unsigned
product_id  int(10) unsigned
title   varchar(255)
description text
star_rating int(1)
status  int(1)
created_at  int(10)

3) tbl_wishlist //which store the user's wishlist details means when user add any product into his/her wishlist that entry saved in this table

id  int(11) unsigned
product_id  int(11) unsigned
user_id int(11) unsigned
status  tinyint(1) unsigned
created_at  datetime

These are my table architecture. I want fetch user's id using group concat according to product id. means if product id is 1 then all the user's id just come up with (1,3,4,5) like this. next is, I want total number of product reviews of each product. for e.g if user id is 1 then product number comes with 1 or 0 if have or don't have.

Actually i want this type of output

id  product_type_id title               description              price  discount_price       product_image      users   total_review
1   1               Product one         Product one description  786       50               product.jpg        1,2         2

I want do this by a single query.I don't want to fetch first half information after that start loop after than get next half information. I think you understand my problem.

Foeign Key Locking Transaction timeout when a second database connection is opened

I'm having a problem involving a database transaction in one class that is timing out due to a secondary database connection being opened within the transaction; the problem started occurring when I added a foreign key constraint. And, testing using:

SET foreign_key_checks = 0;

I've been able to confirm this.

My database class looks like this (I've left off all of the methods):

class Db { 
function __construct($config) {
    $this->config = $config;
}

private function connect($config) {$dsn = 'mysql:host=' . $config['host'] . ';dbname=' . $config['dbname'] . ';charset=utf8';

$options = array(
            // PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_EMULATE_PREPARES => false, 
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
           PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        );

$dbh = new PDO($dsn, $config['username'], $config['password'], $options);
$dbh->exec("SET NAMES utf8;"); 

return $dbh;
 }      
}

My model looks like this:

class Model {

    function __construct() {
        $this->db = new Db(array('host'=>DB_HOST,'dbname'=>DB_NAME,'username'=>DB_USERNAME,'password'=>DB_PASSWORD));
    }

}

The code below then performs a little bit of logic, then an insert into the question_orders table: question_orders has a column question_id, with a foreign key index, which references the parent table questions; I think that the problem is that Assessment_Question_Orders extends the Model and creates a new database connection? Any thoughts on how to maintain both the transaction and foreign key aspects would be appreciated.

  class This_Is_A_Problem() {
       public function problemFunction()  {

    /*variable init code left out*/

    $this->db->beginTransaction();
    $db_result = false;
    try {

    $db_result = $this->db->insert('questions', $questions_data);
    $new_insert_id = $this->db->lastInsertId();

    $assessment_question_orders = new Assessment_Question_Orders();

 $question_number = $assessment_question_orders->insertSingleQuestionOrder($module_id, $new_insert_id);

    $db_result = $this->db->commit();

    }
    } catch (PDOException $e) {

     $this->db->rollBack();

    }}}

using a session to show text for a specific group

This is a part of my session script:

$group='admin';

session_start();
header('Content-type: text/html;charset=UTF-8');
if(!isset($_SESSION['username']) and isset($_COOKIE['username'], $_COOKIE['password']))
{
  $cnn = mysql_query('select password,id,group from users where username="'.mysql_real_escape_string($_COOKIE['username']).'"');
  $dn_cnn = mysql_fetch_array($cnn);
  if(sha1($dn_cnn['password'])==$_COOKIE['password'] and mysql_num_rows($cnn)>0)
  {
    $_SESSION['username'] = $_COOKIE['username'];
    $_SESSION['userid'] = $dn_cnn['id'];
    $_SESSION['group'] = $dn_cnn['group'];
  }
}
?>

I want to show "blablabla" but only if you are in a group:

<?php
if(isset($_SESSION['group']) and $_SESSION['group']==$group)
{
?>
blablabla
 <?php 
 }
 ?>

In my table is a column named as "group". The group of this session is named as "admin" (that's why: $group='admin';) but it doesn't show the "blablabla".

Does someone know what I'm doing wrong?

Get th result depends on the stops names order

I have the following tables below. Is it possible to get the routeand the direction depend on the stops order to avoid the result of the opposite stop? So if I have the following stop's name order as Abc, Def, Ghi the result of my query should just consider alle routes which have this stops order Abc, Def, Ghi as I said to prevent the result of the opposite stop that has arrivale time too.

Tables:

CREATE TABLE IF NOT EXISTS routes (
                    route_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    direction VARCHAR(30) NOT NULL, 
                    route INT(11) NOT NULL )

CREATE TABLE IF NOT EXISTS stops
                    (stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                     name varchar(30) NOT NULL, 
                    lat double(10,6) , 
                    longi double(10,6)  


CREATE TABLE IF NOT EXISTS arrivaltimes(arrivaltimes_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    weekday VARCHAR(20) NOT NULL,
                    route INT(11) NOT NULL, 
                    arrivaltime time NOT NULL,
                    stop_id INT, FOREIGN KEY fk_stop_id(stop_id) REFERENCES stops(stop_id), 
                    route_id INT, FOREIGN KEY fk_route_id(route_id) REFERENCES routes(route_id) 

Query:

SELECT r.route, r.direction FROM routes AS r 
            JOIN arrivaltimes AS a ON a.route_id = r.route_id 
            JOIN stops as s on a.stop_id = s.stop_id 
            WHERE a.weekday = ?
            AND arrivaltime between subtime(curtime(), '00:02:00') and addtime(curtime(), '00:02:00')
            AND s.name = ?

Database connectivity issue

I am trying to connect do database to store the data, but its not getting stored and also its not showing any errors..can anyone help me out. the code is not showing error's same time its not getting stored in database too..not sure what the problem is..or what my mistake is

JSP File:

 <form action="Register">
        <table>
            <tr>
                <td>
                    Name: 
                </td>
                <td>
                    <input type="text" name="n1">
                </td>
            </tr>
            <tr>
                 <td colspan="3" height="15px">
                </td>
            </tr>
            <tr>
                <td>
                    Age: 
                </td>
                <td>
                    <input type="text" name="a1">
                </td>
            </tr>
            <tr>
                 <td colspan="3" height="15px">
                </td>
            </tr>
            <tr>
                <td>
                    Mobile Number: 
                </td>

                <td>
                    <input type="text" name="m1">
                </td>
            </tr>
            <tr>
                 <td colspan="3" height="15px">
                </td>
            </tr>
            <tr>
                <td>
                    Email: 
                </td>

                <td>
                    <input type="text" name="e1">
                </td>
            </tr>
            <tr>
                 <td colspan="2" height="15px">
                </td>
            </tr>
            <tr>
                <td>
                    Username: 
                </td>

                <td>
                    <input type="text" name="u1">
                </td>
            </tr>
            <tr>
                 <td colspan="2" height="15px">
                </td>
            </tr>
            <tr>
                <td>
                    Password: 
                </td>

                <td>
                    <input type="password" name="p1">
                </td>
            </tr>
            <tr>
                 <td colspan="3" width="100px"></td>
            </tr>
            <tr>
                <td><input type="submit" name="sub" value="Register"></td>
                <td><input type="reset" value="Reset" class="subm"></td>
            </tr>
        </table>
          </form> 

Register Servlet: Servlet for storing in the database

try (PrintWriter out = response.getWriter()) {
       String name=request.getParameter("n1");
       String age=request.getParameter("a1");
       String mobile=request.getParameter("m1");
       String email=request.getParameter("e1");
       String username=request.getParameter("u1");
       String password=request.getParameter("p1");
       if(request.getParameter("sub")!=null)

{

 try
    {
        Class.forName("com.mysql.jdbc.Driver");  
        Connection  con=DriverManager.getConnection("jdbc:mysql://localhost:3306/soundstage","root","");
        Statement stmt=con.createStatement();   
        stmt.executeUpdate("Insert into REGISTRATION values('"+name+"','"+age+"','"+mobile+"','"+email+"','"+username+"','"+password+"')");


       }
       catch(Exception e)
       {
           System.out.println(e.getMessage());
       }

       finally
   {
        RequestDispatcher rd=request.getRequestDispatcher("/Home.jsp");
        rd.forward(request, response);
   }
   }

Image of database url, name etc..

http://ift.tt/1ecqjOv

Attendance System Calculate Total Time

I am currently working on an attendance system for my robotics team using mySQL and PHP using CodeIgniter as a framework. I have functionality for signing in and signing out but the first 2 times the user signs in, it displays their total time by subtracting the length of the current session from 24 hours. It's hard to explain. The output is like this for the first few times the user signs out:

X has signed out

Time: 00 hours 0 minutes 8 seconds Total Time: 23:59:52

It subtracts 8 seconds from 24 hours for some reason for the first 2 times the user signs out.

Here's the code (It's very messy and I apologize, this is my first big project in php)

    public function clock_in($pin_number)
    {
        // Used to get user id
        $query = $this->db->get_where('users', array('pin_number' => $pin_number));
        $id = $query->row_array()['user_id'];

        // Used to get Id of Active event
        $query2 = $this->db->get_where('events', array('is_active' => 1));
        $event_id = $query2->row_array()['event_id'];

        // Used to get last clock of user
        $query3 = $this->db->get_where('clocks', array('user_id' => $id));
        $clock_array = $query3->result_array();
        $size = count($clock_array);
        if($size == 0)
        {
            $data = array(
                'user_id' => $id,
                'event_id' => $event_id,
                'time_stamp' => date('Y-m-d H:i:s'),
                'clock_in' => TRUE
            );
            $this->db->insert('clocks', $data);
            echo $this->get_name($pin_number);
            echo " Has signed in for the first time <br>";
            echo "Welcome to the Team!";
            return;
        }
        $result = $clock_array[$size-1];
        $data = array(
            'user_id' => $id,
            'event_id' => $event_id,
            'time_stamp' => date('Y-m-d H:i:s'),
            'clock_in' => $this->is_clock_in($id)
        );
        // Has the user previously clocked in?
        if(!$this->is_clock_in($id))
        {
        //If yes, store the time the user clocked
        $time = new DateTime($result['time_stamp']);
        //Store the current time
        $current = new DateTime(date('Y-m-d H:i:s'));
        $difference = $current->diff($time);

        $time_a = strtotime($result['time_stamp']);
        $time_b = strtotime(date('Y-m-d H:i:s'));
        echo $this->get_name($pin_number);
        //echo $difference->format('%i')/60;
        echo " has signed out<br>";
        echo "<br>";
        if(abs($time_b-$time_a)/60/60 > 16)
        {
            echo "You forgot to sign out<br>";
            echo "You will not be credited<br>";
            echo "You have been automatically signed in";
            $data['clock_in'] = TRUE;
            $this->db->insert('clocks', $data);
            return;
        }
        echo "Time: ";
        //Display how long the user has been signed in
        echo $current->diff($time)->format('%H hours %i minutes %s seconds');
        $totalTime = new DateTime("0-0-0 0:0:0");
        if($size == 0)
        {
            $totalTime->add($current->diff($time));
        }
        for($i = 1; $i < $size; ++$i)
        {
            $row = $clock_array[$i];
            $row2 = $clock_array[$i - 1];
            if($row['clock_in'] == FALSE)
            {   
                $time_stamp = new DateTime($row['time_stamp']);
                echo $time_stamp;

                $last_time = new DateTime($row2['time_stamp']);
                echo $last_time;

                $delta;
                if($size == 0)
                {
                    $delta = $last_time->add($time_stamp);
                }
                else
                {
                    $delta = $last_time->diff($time_stamp);
                }

                echo $delta;
                $totalTime->add($delta);
            }
        }    
        $totalTime->add($current->diff($time));
        echo "<br>Total Time: ";
        echo $totalTime->format('H:i:s');

Any help is greatly appreciated. Thanks.

mySQL how to limit query returning all results containing a substring

lets consider an example. I want to search car and i'm getting all the results right.

But the problem is i also get all the results which contains car as a substring e.g my result also return cartoon, care and every word that contains car as a substring in my database.

What i want is to apply a filter/condition so that it won't return words like cartoon and care, rather it should only return words like car and cars.

How can i achieve that? I have tried below solutions and i know what the problem is but i cannot understand how to solve it

$string.='(tbl_data.ad_title like "%'.$_REQUEST['searchtt'].'%"  or tbl_categories.cat_title like "%'.$_REQUEST['searchtt'].'%" ) and ';

$gtdata = mysql_query(
"SELECT tbl_data.id, tbl_data.main_cat, tbl_data.sub_cat, tbl_data.makevalue, tbl_data.ad_title, tbl_data.additional, tbl_data.city_name, tbl_data.city_area, tbl_data.date1,tbl_data.date2,tbl_data.make_featured_active, tbl_data.make_stoplight_active, tbl_data.make_urgent_active 
FROM tbl_data LEFT JOIN tbl_categories ON tbl_data.main_cat=tbl_categories.id 
where ".$string." tbl_data.status='1' and tbl_data.del=0 and tbl_data.exp=0 and tbl_data.sold=0 and tbl_data.userblock='0' ".$orderby." limit ".$limit_start.",".$limit_end.""
);
while($res_gtdata=mysql_fetch_array($gtdata))
{
//all results are stored in this variable
//$res
}