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
}

How to use Django ORM to function on a field

This question is a follow-up to this one.

I'm running a Django application on top of a MySQL (actually MariaDB) database.

My Django Model looks like this:

from django.db import models
from django.db.models import Count, Sum

class myModel(models.Model):
    my_string = models.CharField(max_length=32,)
    my_date = models.DateTimeField()

    @staticmethod
    def get_stats():            
        logger.info(myModel.objects.values('my_string').annotate(
                count=Count("my_string"), 
                sum1=Sum('my_date'),
                sum2=Sum(# WHAT GOES HERE?? #),
            )
        )

When I call get_stats, it gives me the count and the sum1. However, for sum2, I want the sum of the following Database expression for each matching row: my_date + 0 (this converts it to a true integer value).

What should I put in the expression above to get that sum returned in sum2?

IllegalArgumentException: Type cannot be null

I am confronting with an issue and it seems that many people encountered it and probably couldn't solve it.

I have the following MYSQL stored procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_resource_types`()
BEGIN
    SELECT *
    FROM resource_types
    WHERE inactive = 0;
END

The entity which maps the resource_types table and the named stored procedure query.

@NamedStoredProcedureQuery(
        name="getResourceTypes",
        procedureName="get_resource_types",
        resultClasses = ResourceType.class,
        parameters = {}
)
@Entity
@Table(name = "resource_types")
public class ResourceType {
    ... fields with annotations used for validation + getters and setters ...
}

And here is my JpaRepository from which I make the call to the stored procedure

@Repository
public interface ResourceTypeRepository extends JpaRepository<ResourceType, Long> {
    @Procedure("ResourceType.getResourceTypes")
    List<ResourceType> getResourceTypes();

}

When I try to run this I get the following stack trace:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:144)
    at com.test.ihbs.controller.ResourceTypeControllerTest.test_getAll(ResourceTypeControllerTest.java:111)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:86)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:49)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:64)
    at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:50)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.messaging.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
    at org.gradle.messaging.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
    at com.sun.proxy.$Proxy2.processTestClass(Unknown Source)
    at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:106)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.messaging.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:360)
    at org.gradle.internal.concurrent.DefaultExecutorFactory$StoppableExecutorImpl$1.run(DefaultExecutorFactory.java:64)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:223)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy87.getResourceTypes(Unknown Source)
    at com.ihbs.service.ResourceTypeService.getAll(ResourceTypeService.java:34)
    at com.ihbs.controller.ResourceTypeController.getAllResourceTypes(ResourceTypeController.java:44)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
    ... 58 more
Caused by: java.lang.IllegalArgumentException: Type cannot be null
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.setHibernateType(AbstractParameterRegistrationImpl.java:182)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:131)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:140)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:77)
    at org.hibernate.procedure.internal.PositionalParameterRegistration.<init>(PositionalParameterRegistration.java:41)
    at org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:275)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.registerStoredProcedureParameter(StoredProcedureQueryImpl.java:128)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.newAdhocStoredProcedureQuery(StoredProcedureJpaQuery.java:147)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createStoredProcedure(StoredProcedureJpaQuery.java:110)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.doCreateQuery(StoredProcedureJpaQuery.java:68)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createQuery(StoredProcedureJpaQuery.java:58)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:295)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:486)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 80 more

Any ideas why is this happening and how to fix it?

Export database record as CSV file

Is there any way to export database records as CSV file from Mysql Workbench by using command line syntax?

Basic program to fetch data from mysql using EBean

I have assigned a task to create DB layer using EBean. I have never used Ebean before so firstly I am trying to make a simple program to fetch data from a table. I got help from http://ift.tt/1ICNqKk to make sample program but I am unable to fetch data. I created new Entity Class from Database. Following are the few details about my first EBean project.

ebean.properties

ebean.ddl.generate=true  
ebean.ddl.run=true    
ebean.debug.sql=true  
ebean.debug.lazyload=false
ebean.logging=all  
ebean.logging.logfilesharing=all  
ebean.logging.directory=D:\\logs  
ebean.logging.iud=sql  
ebean.logging.query=sql  
ebean.logging.sqlquery=sql    
ebean.logging.txnCommit=none  
datasource.default=h2    
datasource.h2.username=sa  
datasource.h2.password=  
datasource.h2.databaseUrl=jdbc:h2:mem:tests;DB_CLOSE_DELAY=-1  
datasource.h2.databaseDriver=org.h2.Driver  
datasource.h2.minConnections=1  
datasource.h2.maxConnections=25  
datasource.h2.heartbeatsql=select 1  
datasource.h2.isolationlevel=read_committed    
datasource.mysql.username=root  
datasource.mysql.password=kalsym@123  
datasource.mysql.databaseUrl=jdbc:mysql://127.0.0.1:3306/wsp  
datasource.mysql.databaseDriver=com.mysql.jdbc.Driver  
datasource.mysql.minConnections=1  
datasource.mysql.maxConnections=25   
datasource.mysql.isolationlevel=read_committed

Code to fetch data

RoutingAlgoType routingObj = new RoutingAlgoType();
routingObj.setName("LCR");
RoutingAlgoType routingObj2  = Ebean.find(RoutingAlgoType.class, routingObj);
System.out.println("Got "+routingObj2.getDescription());

Table Data

Insert into routing_algo_type (name, description) values ('LCR', 'Least Cost Routing');

When I execute this code I get null pointer exception. It feels to me that mysql connection was not established successfully. Is there any way to verify db connection

Socialengine 4.8.6 on AWS (EC2, RDS (MYSQL/INNODB), S3 and CLOUDFRONT) - White Screen on Startup/ possible INNODB issue

Socialengine 4.8.6 - shows a 'white screen' on startup, only the http://ift.tt/1TWwvLy and sesystem.com/install can be accessed via the browser.

Problem started: I needed a new database with a copy of production data to test a new upgrade (Socialengine 4.8.9). I used PHPMYADMIN to copy the production db(PROD) to a new developement db (COPYOFPROD), both databases reside on AWS RDS, same instance and user/password, and both connect via INNODB.

Changed social/application/settings/database.php on my system from PROD to COPYOFPROD to test the database was correct (Yes - not very clever with aproduction system!). Got a white screen when trying to access the system via the browser with the new database.

Reverted the single config change on /application/settings/database.php to the original setting. The White screen remains and looking at the Socialengine error logs, it reports it doesn't recognise the PROD database anymore. No other file in the Socialengine install directories have changed.

Status : Access to the RDS databases on phpmyadmin is ok Accessing socialengine, http://ift.tt/1TWwvLA then select 'requirements and redundancy check' the System reports the following:

MySQL 'OK'

MySQL 4.1 'Unable to check. No database adapter was provided.'

MySQL InnoDB Storage Engine 'Unable to check. No database adapter was provided.'

I therefore summarise that i have crashed the INNODB service with my actions. I understand the service is sensitive and will crash if you change config entries.

I have read that the INNODB log files need to be removed before the service will resume, i have tried the procedure of removing the ib_logfiles and restarting mysql. Result : mysql start [ok], the ib_logfiles are recreated, but when i try Socialengine it still has a 'white screen' and still reports 'No database adaptor'.

My questions are :

1) How do i check INNODB services are running correctly on AWS EC2/RDSMYSQL? Note: I'm using Terminal from a OSX Machine connecting to EC2 via the standard ECUSER and a pemkey combination.

2) How do i access mysqlmonitor on AWS RDS with the appropriate permission problems to make checks on INNODB status. Currently the system reports - you need 'PROCESS' rights when trying show status commands.

3) Which are the best logs in Socialengine to see why the whitescreen is happening and are there any tips - because i am only assuming this is the INNODB issue but i need to confirm it.

I am a novice, so not sure what my next steps are..

Many Thanks

Convert 'yyyy-mm-dd' strings to date type variables for MySQL from Java

I am using JXL API for reading in the values from Excel File. I want to convert the above specified format into Date type Variable to load into MYSQL Database. Please Help me.

![Error Shown: Exception in thread "main" java.lang.Error: Unresolved compilation problem: 
Type mismatch: cannot convert from String to Date

at SoccerDatabase.read(SoccerDatabase.java:74)
at SoccerDatabase.main(SoccerDatabase.java:122)][1]


static Date[] dob=new Date[1000];
Cell cell5=sheet.getCell(4,i);
dob[i]=cell5.getContents();

Designing a database for storing 500 million domain names with full text search

I'm about to build an application that stores up to 500 million records of domain names. I'll index the '.net' or '.com' part and strip the 'www' at the beginning. So I believe the table would look like this:

domain_id | domain_name  | domain_ext
----------+--------------+-----------
1         | dropbox      | 2
2         | digitalocean | 2

domain_ext = 2 means it's a '.com' domain.

The queries I'm about to perform::

1. I need to be able to insert new domains easily.
2. I also need to make sure I'm not inserting a duplication (each domain should have only 1 record), so I think to make domain_name + domain_ext as UNIQUE index (with MySQL - InnoDB).
3. Query domains in batches. For example: SELECT * FROM tbl_domains LIMIT 300000, 600;

What do you think? will that table hold hundreds of millions of records? How about partitioning by first letter of the domain name, would that be good? Let me know your suggestions, I'm open minded.

PHP Code Doesn't insert data into the database on the web (godaddy) but works fine in localhost

I have written code that inserts some text data and also an image file through a form. the form processor code does not have to only insert and upload data but also create thumbnail for the image.
Now everything for image upload and creating the thumbnail works fine BUT when it comes to database and inserting the data into the table it doesn't insert data. I checked my database connection it is working fine. I also checked the same code in my localhost computer it works fine there too.
Note: I don't get any errors but it doesn't insert data into the database. I use godaddy.
Any cooperation and help will be highly appreciated.
Since the complete code is too long, Here is the code portion that has to insert data into the database.

include 'dbcon.php';
    //error_reporting(E_ALL ^ E_NOTICE);
    if(isset($_POST['submit']))
    {

    $file = ($_FILES['file']['name']);
    $author = $_POST['author'];
    $album = $_POST['name'];
    $imgdetails = $_POST['imgdetails'];
    $details = $_POST['details'];
    $datetime=date("M d, Y"); //date time 
    $jalali_date = jdate("l d p Y",$timestamp);
    $query = "INSERT INTO `fslide` (`picture`,`author`,  `album`, `imgdetails`, `details`, `date`, `fdate`) 
                                       VALUES ('$file', '$author', '$album', '$imgdetails', '$details', '$datetime', '$jalali_date');";
    $sql = mysql_query ($query);

data truncated in column error, probably a type error, how to avoid it?

I'm doing a summary table where I am creating a new table aggregating by average. Tiny example shown here:

CREATE   TABLE PURCHASES (U_ID INT,  TIMECODE TINYINT, PURCHASEVALUE INT);
INSERT INTO PURCHASES VALUES 
    (3399, 1, 10), (3399, 2, 20),
    (2222, 2, 20), (2222, 2, 30),
    (6644, 1, 10), (6644, 1, 30), (6644, 3, 10), (6644, 2, 60),
    (4000, 4, 399);

CREATE TABLE PURCHASES_BY_TIMECODE
    SELECT TIMECODE, AVG(PURCHASEVALUE) AS AVG_PURCHASEVALUE
    FROM PURCHASES
    GROUP BY PURCHASES.TIMECODE;

I get the warning error message "Data truncated for column AVG_PURCHASEVALUE". When I look at the data types of my new table, I see:

TIMECODE tinyint(4)

AVG_PURCHASEVALUE decimal(14,4)

My guess is that the avg function is returning something that doesn't fit into the decimal(14, 4) that mysql has invented for the data type of the 2nd column.

The results look sensible but I would prefer to code it up so that I didn't get this error. I think that what I need to do is either (a) explicitly specify the column data type for my table which I now do with a create/select, or (b) round the average with zero decimals so it fits into int and I avoid data type issues by ducking them.

Two problems with approach (a):
1. I am not sure how to define the column data type via a create/select statement - or whether this is even possible to do, syntactically - the only uses I've run into of create/select don't explicitly declare column types

2. assuming I can find a suitable syntax for declaring the type, I am not sure what type to cast it into so that the error will go away and the result of the avg computation will be of the same type, as I am not sure what this type is, or whether it can be specified somehow in the function

Two questions:

1. Is approach b the most fit for purpose for me in this circumstance? In this instance I don't actually care about decimal points. (But in some other use case I might.)
2. How would I go about doing approach a? (in case it fits some other scenario I run into in future)

thanks!

p.s. needless to say, I am new to sql...

Default value for DATETIME type NOW() + 7 days

When creating a new table like this:

CREATE TABLE myTable (
   id INT(11) AUTO_INCREMENT,
   deadline DATETIME,
   PRIMARY KEY(id)
)

I want to set a DEFAULT value for deadline, such that it will be NOW() + 7 days, how can I achieve this?

I've tried this:

deadline DATETIME DEFAULT (NOW() + INTERVAL 7 DAY)

and this:

deadline DATETIME DEFAULT DATE_ADD(NOW(), INTERVAL 7 DAY)

where none of them seems to be working.

Upload is reading PHP variable as null

I am having trouble getting a picture to load from mysql database. The directory is randomly generated and gets stored in the database just fine. When the page refreshes the img returns a broken link, echos 'not set.', and inspect element tells me that $default_pic isn't defined. I can't figure out what is going on here can anyone help?

<?php
error_reporting(E_ALL); ini_set('display_errors', 1);
$listingid = $_SESSION['edit_listing'];
if(isset($_FILES['listingpic'])){
    if($_FILES['listingpic']['type']=="image/jpeg"||$_FILES['listingpic']['type']=="image/png"||$_FILES['listingpic']['type']=="image/gif"){
        if($_FILES['listingpic']['size']<1048576){
            $chars = "abcdefghijklmanopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";
            $rand_dir_name = substr (str_shuffle($chars), 0, 15);
                                    mkdir("userdata/listingpics/$rand_dir_name/") or die("directory error");
            if(file_exists("userdata/listingpics/$rand_dir_name/".$_FILES['listingpic']['name'])){
                echo "File already exists.";
            }
            else{
                move_uploaded_file($_FILES['listingpic']['tmp_name'], "userdata/listingpics/$rand_dir_name/".$_FILES['listingpic']['name']) or die("Failed to move file.");
                $listing_pic_name = $_FILES['listingpic']['name'];
                $listing_pic_query = mysql_query("UPDATE properties SET default_pic='$rand_dir_name/$listing_pic_name' WHERE id='$listingid'"); 
                $default_pic = "userdata/listingpics/$rand_dir_name/".$_FILES['listingpic']['name'];//<-PROBLEM
                header("Location: ../list_property/upload.php?id=".$listingid);
            }
        } else echo "File must not exceed 1MB.";
    } else echo "File must be a JPEG, PNG, or GIF image.";
} else echo "Not set.";

?>
<form action="" method="POST" enctype="multipart/form-data">
    <img src="<?php echo $default_pic; ?>" width="50%" height="50%"/><br>
    <br>
    <input type="file" name="listingpic" />
    <input type="submit" name="uploadpic" value="Upload Picture">
</form>

How can I update json automatically with php whenever mysql db is changed

I need exactly this, to change the .json I am generating on every change of the DB. Can anybody tell me how I can go about doing this? Also, as a side note, how can I enable utf-8 encoding in this?

This is my code right now:

$return_arr = array();
include("connect.php");
header('Content-Type: text/html; charset=utf-8');
$name = $_GET['name'];

$fetch = "SELECT * FROM {$name}";
if ($result = mysqli_query($conn,$fetch)) {
    while($row = $result->fetch_assoc()) {
        $myArray[] = $row;
    }
}
$json = json_encode($myArray,JSON_UNESCAPED_UNICODE);

$fp = fopen('nlb.json', 'w');
fwrite($fp, $json);
fclose($fp);
$conn->close();

PS is there any way to automatically generate a php script on an interval of idk like 1 hour? That would be a nice workaround, but not very effective since my DB has over 100 entries.

Redis and mysql database

Im currently using Redis in my webProject. But i would like to dump the redis into mysql, so if redis result are empty, get the result from mysql and store it to redis.

How would i proceed to do this? Should i do one or two?

After editing/inserting into Redis, do the same to mysql

redis -> dump to mysql -> get from mysql if empty.

if so, how would i do this? or any other suggestions on how i should do the mysql as a backup if redis result is empty?