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?

Can't figure out this array [duplicate]

This question already has an answer here:

• How can I access an array/object? 3 answers

I have an array:

Array ( [variants] => Array ( 
    [0] => Array 
        ( [barcode] => 610373772697 [compare_at_price] => [created_at] => 2015-06-27T01:56:42-07:00 [fulfillment_service] => manual [grams] => 190 [id] => 3856358467 [inventory_management] => shopify [inventory_policy] => deny [option1] => Default Title [option2] => [option3] => [position] => 1 [price] => 20.99 [product_id] => 1259972867 [requires_shipping] => 1 [sku] => 1125442 [taxable] => 1 [title] => Default Title [updated_at] => 2015-06-27T01:56:42-07:00 [inventory_quantity] => 100 [old_inventory_quantity] => 100 [image_id] => [weight] => 0.42 [weight_unit] => lb ) 
    [1] => Array 
        ( [barcode] => 364031530906 [compare_at_price] => [created_at] => 2015-06-27T01:56:42-07:00 [fulfillment_service] => manual [grams] => 131 [id] => 3856359043 [inventory_management] => shopify [inventory_policy] => deny [option1] => Default Title [option2] => [option3] => [position] => 1 [price] => 17.24 [product_id] => 1259973059 [requires_shipping] => 1 [sku] => 0116350 [taxable] => 1 [title] => Default Title [updated_at] => 2015-06-27T01:56:42-07:00 [inventory_quantity] => 100 [old_inventory_quantity] => 100 [image_id] => [weight] => 0.29 [weight_unit] => lb ) 
    [2] => Array 
        ( [barcode] => 364031534003 [compare_at_price] => [created_at] => 2015-06-27T01:56:43-07:00 [fulfillment_service] => manual [grams] => 390 [id] => 3856359107 [inventory_management] => shopify [inventory_policy] => deny [option1] => Default Title [option2] => [option3] => [position] => 1 [price] => 27.74 [product_id] => 1259973123 [requires_shipping] => 1 [sku] => 0116368 [taxable] => 1 [title] => Default Title [updated_at] => 2015-06-27T01:56:43-07:00 [inventory_quantity] => 100 [old_inventory_quantity] => 100 [image_id] => [weight] => 0.86 [weight_unit] => lb ))
    ) 
) 

And no matter how many For Loops I try, I cannot figure out how to extract the [id] and the [product_id] and pass them to variables using PHP.

My goal is to dump the id and product_id into a mysql database and I once I get the values out I can do that easily.

Can anyone help guide me?

How do i run a simple php file with wamp?

It is after much frustration that I write this question here. Couldn't possibly figure it out on my own. I recently downloaded the book 'Head first PHP and MySQL' and I'm trying to run the php files from a local webserver. For that I've downloaded wamp server 2.5 (Apache : 2.4.9 MySQL : 5.6.17 PHP : 5.5.12) in order to simplify the installation process of installing all the software components separately. I've gone all over the possible tutorials online and nothing has worked for me. Tried using Virtual hosts as well, but to no avail. Whenever I try to load any page through localhost or through a Virtual host, I get the 404 Forbidden: 403 Forbidden You don't have permission to access / on this server error.

Can someone please give me a beginner instructions as to what exactly I have to do? Consider the fact that I've just installed wampserver with all its default settings.

mysqli sum gets me a error so mabe someone can?

hi i have the following:

wntl_virtuemart_order_items 

product_quantity
order_item_name
order_item_sku

I connect with the DB run this php below

$count="SELECT wntl_virtuemart_order_items, product_quantity sum(product_quantity ) as total 
        from wntl_virtuemart_order_items 
        group by order_item_name";
echo "<table>";

echo "<tr><th>order_item_sku</th><th>order_item_name</th><th>product_quantity</th></tr>";

foreach ($dbo->query($count) as $row) {

echo "<tr ><td>$row[order_item_sku]</td>
           <td>$row[order_item_name]</td>
           <td>$row[product_quantity]</td>
           <td>$row[total]</td></tr>";    
}

echo "</table>";

$conn->close();
?>

and i get the error:

Call to a member function query() on a non-object in 23

What am i doing wrong?

My plan was to know how much products where ordered per item.

Automatically change a php form row value by changing another row value

I have a ticketing support system and I have some rows when in call_edit.php file. I have 2 rows called staff and status . When someone sends us a ticket, the ticket status by default is open and the staff is row is empty (0 in my code) . I want to achieve that when I change the staff value from empty (0 in my code) to a name (kitty, John or else), the status automatically changes from open to Checking.

This is some of my php form:

<tr><td valign="top" style="width: 150px;">Status</td>
<td><select name='call_status'>
<option value='0'<?php if($site_calls->call_status == 0){echo ' selected';}?>>Open</option>
<option value='2'<?php if($site_calls->call_status == 2){echo ' selected';}?>>Checking</option>
    <option value='1'<?php if($site_calls->call_status == 1){echo ' selected';}?>>Closed</option>
    <option value='3'<?php if($site_calls->call_status == 3){echo ' selected';}?>>Deleted</option>
    </select> 
    </td></tr>

<tr><td>Staff</td><td><select name='call_staff'>
<option value="0"></option>
<?php $staff_name = $db->get_results("select user_id,user_name from site_users where user_level<>1 order by user_name;");
foreach ($staff_name as $staff )
{?>
<option value='<?php echo $staff->user_id;?>'<?php if($staff->user_id == $call_staff){echo ' selected';}?>><?php echo $staff->user_name;?></option>
<?php } ?>
</select></td></tr>

How to join a table without any common field in mysql?

Below are the tables I'm working on,

tableA

╔════╦══════════════╦══════╗
║ ID ║  REVLIMIT    ║ REV  ║
╠════╬══════════════╬══════╣
║  1 ║ 8500         ║  69  ║ 
╚════╩══════════════╩══════╝

tableB

╔════╦══════════════╦ 
║ KID║   Model      ║  
╠════╬══════════════╬ 
║  1 ║ 43453453345  ║  
║  2 ║ 23423423     ║  
║  2 ║ 5566533      ║  
╚════╩══════════════╩ 

I need to join table with table B so that I get the below result

tableC

╔════╦══════════════╦══════╗
║ KID║  Model       ║ REV  ║
╠════╬══════════════╬══════╣
║  1 ║ 43453453345  ║  69  ║
║  1 ║ 2433423423   ║  69  ║
║  2 ║ 23423423     ║  69  ║
║  2 ║ 5566533      ║  69  ║
╚════╩══════════════╩══════╝

ie all the Model in tableB should have REV as 69 in this case.

I can use only plain MySQL query,How to accomplish that? Thanks in advance

mysql 5.6 drop primery key not working

I want to drop primery key constraint from my table.

mysql> alter table 'carpool' drop PRIMERY KEY, add PRIMERY KEY('pool_id'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''carpool' drop PRIMERY KEY, add PRIMERY KEY('pool_id')' at line 1

I have checked mysql manual and other similar question in stack overflow but none is able to solve my problem

Any help will be appreciated

Using MIN() in SET statement MySQL

I am using MySQL. Lets call a table that I have as Inventory which looks is below:

+----+--------+--------+-------------+----------+ | ID | Price1 | Price2 | TargetPrice | Quantity | +----+--------+--------+-------------+----------+ | 1 | 12 | 1 | | 0 | | 2 | 3 | 3 | 3 | 2 | | 3 | | 4 | | 0 | | 4 | 2 | 2 | 2 | 2 | | 5 | 5 | 45 | 5 | 1 | +----+--------+--------+-------------+----------+

Now, I need to update the TargetPrice to minimum of Price1 and Price2 for any row whose Quantity is 0

I have tried:

UPDATE Inventory SET TargetPrice= MIN(Price1,Price2) WHERE Quantity >0

However, MySQL complains about the usage of MIN() function. I know it is expecting MIN() to work on the data contained inside column, rather than taking MIN() of two columns of a specified row.

Anyway to achieve this other than cursors?

NOLOGGING is not functioning

This is a part of my code. Everything is ok, no error. But I have million of lines to be inserted. I searched the internet for ways to finish my job faster.

What I found, is that insert in table using nologgin and +append, but is not working. Time to insert the same lines in table is the same even I use nologging and append.

    create or replace procedure read_files(input varchar2, extensie varchar2) as  
........................................ 
    Loop
     BEGIN
..............................
        UTL_FILE.GET_line(F1,V1);
    insert /*+ append */ into alarms(alarm_id,property_name,property_value) 
    =values(alarm_counter,f_property_name,f_property_value) ;

     End loop;
    end; 


alter table alarms nologging;
execute read_files('occ','cap');
alter table alarms logging;

Steps for my work:

• first compile the procedure
• alter table nologging
• execute procedure

Where is my mistake?

Web service in Rails to handle two databases

I asked in a question LINK how it was the best way to manage two databases MySQL for a Rails Web App. I searched "web services" in internet and i found a lot of things. My questions are:

1) I have to program an app in Rails, is it usefull to create the web service for this app in Rails too?

2) I have to use two databases , in my Web service how can i have to manage this?

for example, i need the list of the users of the DB1, so i write:

http://localhost:3000/users/

but how do i have to program the web service to be able to separate the two databases? I mean , if i need the users od the DB2 i would use an url like the before one:

http://localhost:3000/users/     (but for DB2)

3) I know that in a web service we should create the WSDL file. But is really necessary in my case? i will not publish the web service so , i think is not necessary to create the WSDL. is it?

Sorry for the newbie questions but it's my first time in web service programming.

(JSP) Writing UTF8 to MySQL won't work

I am stuck for days and I can't find a suitable post here.

I am trying to write russian characters to MySQL 5.5 using JSP (JSTL). While it works on my own localhost, I can't get it to work on the server I rented.

It's sending the right params from a form to another page, which then shows the output correctly. This also works on the server. Writing/Reading to and from DB only works on my own localhost.

If I try to write to DB on the server, russian characters turn into question marks. However, I can store russian characters using phpMyAdmin, which then display correctly on my web page.

Here is my query and what I've tried so far: Query:

    <sql:update dataSource="${ds}" var="updatedTable">
        INSERT INTO Question (question) VALUES (?) 
        <sql:param value="${param.questionAsked}" />
    </sql:update>

DBconn:

    <sql:setDataSource var="ds" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/myDBname?useUnicode=true&characterEncoding=utf-8" user="secret" password="secret" />

Page Encoding in every JSP file:

    <%@page language="java" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

Add this to tomcat server.xml:

    <Connector port="8080" maxHttpHeaderSize="8192"
maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
enableLookups="false" redirectPort="8443" acceptCount="100"
connectionTimeout="20000" disableUploadTimeout="true"
URIEncoding="UTF-8"
/>

Uncommented the CharacterEncodingFilter in web.xml:

    <filter>
    <filter-name>setCharacterEncodingFilter</filter-name>
    <filter-class>org.apache.catalina.filters.SetCharacterEncodingFilter</filter-class>
    <init-param>
        <param-name>encoding</param-name>
        <param-value>UTF-8</param-value>
    </init-param>
    <async-supported>true</async-supported>
</filter>
    <filter-mapping>
    <filter-name>setCharacterEncodingFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>

And altering/creating new DB's and DB tables for test with utf8:

CREATE TABLE `Question` (
  `questionID` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `question` varchar(255) DEFAULT NULL,
  `categoryID` int(6) unsigned DEFAULT NULL,
  PRIMARY KEY (`questionID`),
  KEY `categoryID` (`categoryID`),
  CONSTRAINT `question_ibfk_1` FOREIGN KEY (`categoryID`) REFERENCES `Category` (`categoryID`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

I guess my problem is somewhere in the MySQL connector. I could not quite figure out how to change it since I don't have access to the config files of mysql. I requested SSH access if that helps me. I'm fairly new to such issues since I haven't been dealing with databases and especially servers for too long.

phpMyAdmin shows:

character set client - latin1, (session value - utf8) character set connection - latin1, (session value - utf8) character set database - latin1 character set results - latin1, (session value - utf8) character set server - latin1 character set system - utf8 collation connection - latin1_swedish_ci (session value - utf8_unicode_ci) collation database - latin1_swedish_ci collation server - latin1_swedish_ci

I hope you guys know what to do...

How to Design Optimised Database Structure

I was working on eCommerce site and i am unable to optimize my database.Problem is:Suppose i have 10 venders with same products but with different product price. I want to design the database structure,but unable to get the optimised database structure.Further the venders can increase.If anyone with good database knowledge please help me to solve this problem.Thanks in advance.

SELECT query with apostrophe

In my database, I have a column named storeName with a value called Joe's Kitchen.

When user enters Joe's Kitchen, I would store it in a variable named storeName and do a select query on it like this: "SELECT * FROM shops WHERE storename='".$storeName."'". Problem now is that the value contains apostrophe, how should I go about this ?

I have tried the method below but it is not working

$storeName = mysqli_real_escape_string($db->getConnection(),$_POST["storeName"]);

create two connections MySQL on Rails project

I am creating a CRM(Customer relationship management) in Rails. I have to use two databases(MySQL).I have a doubt in the correct use of these databases.

I mean, i read on internet that is possible to open two connections in a Rails project, but is this the right way to hit the problem? is really good to open two connection in a project?

is it used?if not, what is the solution in the state of art(maybe the simpler to manage queries) for my problem?

When to use RDS service in amazon? I have mysql installed in ec2 instance

I have a dynamic website and I need to migrate to AWS. I am new to AWS and linux. I have a doubt while setting the environment. I have installed Mysql,Phpmyadmin separately.

I have the following questions :

1. How to connect this installed Mysql with this installed Phpmyadmin ? How to access Phpmyadmin through browser in aws.
2. Why do we need RDS then? Do I really need RDS instance?

Please help me..

Alternative for mydac and unidac

I just want to ask if there are other ways to connect my mobile app in a remote MySQL database other than UniDac or myDac which is free.

Im using delphi xe8 for development.

I already research a lot about these, and I also tried a lot of options like firedac or dbexpress, still not getting any solutions. i tried mydac and it works, the problem is for now, i cant pay for the license so im looking for an alternative to mydac or unidac.

most research results points me to firedac, dbexpress or devart's liteDAC which is also licensed. what I did is this, i bought a host in a cloud server provider which I can create a remote database on it. so I created a database in there, which I will use it for my mobile app. i cant use firedac, it says i needed to get libmysqlclient.so or libmysqld.so and put it on a the path where the exe is, i just wonder, what could be the exe that xe8 points here?

i cant even use dbexpress also. when I open my app, It just give me a blackscreen, and dont even show the form. I tried to wait for a minute or two, hoping that it was just trying to connect to the database, still in the same black screen. i put the configuration process of the dbexpress to the form's show event.

thanks a lot for any help.

how to repair a mysql table in c#

I am a php programmer and a few days I am working C#. I wrote a simple mysql database repair application with c# i have just a problem i cant repair selected database

  private void repairBtn_Click_1(object sender, EventArgs e)
    {

       var result = databaseList.SelectedItem;
       //when echo result variable system shown user selected table name

       MessageBox.Show("Table successfully repaired: " + result);
    }

How to use $query->set in a custom .php page?

this is my custom.php file.

require_once("wp-load.php");
global $wpdb;

$query->set('posts_per_page', 50 );           
$query->set('orderby', 'meta_value_num');
$query->set('meta_key', '_thumbs_rating_up');
$query->set('date_query', array( 'after' => '1 month ago' ));   
$query->set('order', 'DESC');   

How can a start this query now?

group_concat only display's one column?

I have a database structure like so

 Car
 - id   
 - carname
 - image
 - category
 - status

Parts
 - partid
 - partname

CarParts
 - carpartid
 - carid(fk)
 - partid(fk)
 - amountid(fk)

Category
 - id
 - categoryname

Amount
 - amountid
 - amountvalue

and here is my query:

SELECT group_concat(parts.partname) as parts FROM car left join  carparts on car.id = carparts.carpartid 
left join  parts on parts.partid = carparts.carpartid
left join amount on amount.amountid = carparts.amountid where status = 1
group by car.id

but this only display parts and nothing else i want

• carname
• image
• category
• status

PHP Not Inserting Content in mySQL Database: Text, Images, Anything

So here is my dilemna that I've been reviewing and trying to break through for the last few days. I've created a basic login/register PHP system, which works fine. I've implemented a blog system that displays posts. I've written an add post function which does not post to the database, and it doesn't throw back an error function either.

I don't really understand because my register system works and adds new users, but the 'add blog post' does nothing. I can add from the database and it displays fine, but nothing here.

<?php
    error_reporting(E_ALL & ~E_NOTICE);
    session_start();

    if (isset($_SESSION['id'])) {

        $userId = $_SESSION['id'];
        $username = $_SESSION['username'];
    } else {
        header('Location: login.php');
        die();
    }

    if ($_POST['submit']) { 
        $title = strip_tags($_POST['title']);
        $subtitle = strip_tags($_POST['subtitle']);
        $content = strip_tags($_POST['content']);

        mysqli_query($dbCon, $userREQ3);
        $userREQ3 = " INSERT INTO `logindb`.`blog` 
        (`title`, `subtitle`, `content`) VALUES ('$title','$subtitle','$content')";

    }
?>

<!DOCTYPE html>
 <html>

   <head>

   </head>

   <body>
     Welcome, <?php echo $username; ?>, You are logged in. Your user id is <?php echo $userId; ?>.

    <a href="index.php">Index</a>
    <form action="logout.php">
        <input type="submit" value="Log me out!">
    </form>

    <form method="post" action="admin.php">
        Title: <input type="text" name="title"/><br>
        Subtitle: <input type="text" name="subtitle"/><br>
        <br>
        <br>
        Content: <textarea name="content"></textarea>
        <input type="submit" value="Write Post"/>
    </form>

   </body>
</html>

Inserting a time into mysql database in java

I'm wanting to insert a time which is taken from a textbox to the mysql database TIME column. I suppose I need to convert String to TIME like converting String to Date in mysql using "STR_TO_DATE" in the query. I looked for answers but I didn't get the answer I required. If anyone could suggest me a method for that would be really helpful.

Image retrieval from mysql database in php not working

I am getting an image from mysql database and trying to show it. I am using the following code. But its not working .What's the mistake i am making?

while ($row=mysql_fetch_array($result,MYSQL_ASSOC)) {
    $photo=$row['photo'];
    $name=$row['firstname'].' '.$row['lastname'];
    $email=$row['email'];
    echo "<tr><td>".'<img src="data:image/jpeg;base64,<?php echo base64_encode( $photo ); ?>" />'.'</td><td>'.$name.'</td><td>'.$email.'</td></tr>';
}

Fatal Error call to member function count() is non object

I am trying to query a database using pdo, but I cant figure out the problem. I have created an init file for my db details and server details and config file for configuration and index file and DB file.

index.php

<?php
  require_once 'core/init.php';

  $user =  Db::getInstance()->get('users',array('username', '=' , 'raja' ));

  if($user->count()) 
  {
      echo "No user";
  }
  else{
    echo "OK!";
  }


?>

Db.php

<?php
    class Db
    {
        private static $_instance = null;
        private $_pdo,
                $_query,
                $_error=false,
                $_results,
                $_count=0;
        private function __construct()
        {
            try
            {
                $this->_pdo =new PDO("mysql:host=" .Config::get('mysql/host') . ";dbname=" .Config::get('mysql/db'),Config::get('mysql/username'),Config::get('mysql/password'));
            }
            catch(PDOException $e)
            {
                    die($e->getMessage());
            }

        }

        public static function getInstance()
        {
            if (!isset(self::$_instance)) 
            {
                self::$_instance = new Db();
            }
            return self::$_instance;
        }
        public function query($sql,$params=array())
        {
            $this->_error = false;
            if($this->_query = $this->_pdo->prepare($sql))
            {   
                $x=1;
                if (count($params)) 
                {
                    foreach ($params as $param ) 
                    {
                        $this->_query->bindValue($x,$param);
                        $x++;
                    }
                }
                if ($this->_query->execute())
                 {
                        $this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
                        $this->_count = $this->_query->rowCount();
                 }  
                 else 
                 {
                    $this->error=true;
                 }
            }
            return $this;               
        }
        public function action($action,$table,$where=array())
        {
            if(count($where) === 3)
            {
                $operators = array('=','<','>','>=','<=');

                $field     = $where[0];
                $operator  = $where[1];
                $value     = $where[2];

                if(in_array($operator,$operators))
                {
                    $sql = "{$action}FROM{$table} WHERE {$field} {$operator} ?";

                    if($this->query($sql,array($value))->error()){
                        return $this;
                    }
                }
            }
            return false;

        }
        public function get($table,$where)
        {
            return $this->action('SELECT *', $table, $where);
        }
        public function delete($table,$where)
        {
            return $this->action('DELETE ', $table,$where);
        }

        public function error()
        {
            return $this->_error;
        }
        public function count()
        {
            return $this->_count;
        }
    }
?>

It reports a fatal error about not finding the count object:

Fatal error: Call to a member function count() on a non-object in C:\xampp\htdocs\Student Management system\index.php on line 6

My program in php is not working [on hold]

This program i have been debugging:

---

<?php
$SQLHost="localhost" ;
$SQLDBName="webgeein_database";
$SQLUser="webgeein_root" ;
$SQLPass="gaurav_singh"; 


$conn = new mysqli($SQLHost, $SQLUser, $SQLPass );
if ($conn->connect_error) 
{
$strErr = sprintf ( "Could not connect to server [%s] Error Code [%d]     \nError Message [%s]", $SQLHost, $conn->errno, $conn->error);
echo ( $strErr);
die ();
}

$conn->select_db ($SQLDBName);
$query = "SELECT * FROM student_record WHERE Name='RAMAN'" ;
if($query){echo("success");}else{echo("failure");} 

$result = $conn->query($query, MYSQLI_STORE_RESULT );
if($result){echo("success");}else{echo("failure");} 

$row = $result->fetch_assoc();
if($row){echo("success");}else{echo("failure");} 

$AGE = $row ['Age'] ;
if($T_Status){echo("success");}else{echo("failure");} 

echo($AGE);

?>

but even then the response is failure at running the query function.

and the result shown is

"""""successfailure."""""

I hope someone would solve the problem.

enter code here

MySQL syntax error (INSERT) [duplicate]

This question already has an answer here:

• Syntax error due to using a reserved word as a table or column name in MySQL 1 answer

I need help with a simple MySQL insert query. I have the following code:

INSERT INTO reads (location, meter, value) VALUES ('$_GET[location]', '$meter', '$value')

Which, after PHP, translates into:

INSERT INTO reads (`location`, `meter`, `value`) VALUES ('1234','1111','2505')

This query returns the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'reads (location, meter, value) VALUES ('1234','1111','2505')' at line 1

I have gone over my syntax many times and just cant figure out what is wrong with my query. Can anyone spot it?

Relational Select with condition

This is the select I'm doing:

$query = "SELECT gestiondoc_publicados_tags.tag FROM gestiondoc_publicados_tags, gestionDoc_publicados_permisos WHERE gestionDoc_publicados_permisos.id_publicado = gestiondoc_publicados_tags.id_publicado AND gestionDoc_publicados_permisos.cargo = :cargo";

The table gestiondoc_publicados_tags is like:

| id | tag | id_publicado |
| 1  | tag1|       1      |
| 2  | tag2|       1      |

The table gestiondoc_publicados_permisos is like:

| id | cargo   | id_publicado |
| 1  | cargo1  |       1      |
| 2  | cargo2  |       1      |

I want to get every tag where the id_publicado is equal to the id_publicado where cargo is equal to cargo1, but I'm only getting tag1 as the result, how can I do the query properly?

Php include file in different folder that includes another file

I am new in PHP and I've been trying to make a file (index.php) include another file (connect.php) that includes another file (config.php) but they are located in different folders.

This is my directory structure:

 > index.php (in the [root]...)
 > connect.php ([root]/admin/)
 > config.php ([root]/admin/)

This is what I have so far:

index.php

include './admin/connect.php'

connect.php

$directory = getcwd();
chdir(__DIR__);
include "config.php";
chdir($directory);

This is actually working but somehow I'm not loving the idea of changing the working directory.

Is there a better way to achieve my goal?

Login failed in my code after inserting proper login details (i.e username and password), code in php and jquery

I have following code (php and jquery) for Login for Student and Teacher (using same form for both access). In my system the admin can create Student and Teacher. Once created, the details are saved into database. The saved details is suppose to be use for login to their admin panel. But, the problem is , when Student or Teacher wants to login with the login details, provided by the admin (which has already been saved in database table), It display error message : Login Failed, Please check your username and password. (Same details, saved into database table is used for login process). This aching my head. If someone can tell me , if there is some error in my code, will be much appreciated.

login_form.php

<form id="login_form1" class="form-signin" method="post">
<h3 class="form-signin-heading"><i class="icon-lock"></i> Sign in</h3>
<input type="text" class="input-block-level" id="username" name="username" placeholder="Username" required>
<input type="password" class="input-block-level" id="password" name="password" placeholder="Password" required>
<button data-placement="right" title="Click Here to Sign In" id="signin" name="login" class="btn btn-info" type="submit"><i class="icon-signin icon-large"></i> Sign in</button>
<script type="text/javascript">
$(document).ready(function(){
$('#signin').tooltip('show');
$('#signin').tooltip('hide');
});
</script>
</form>
<script>
jQuery(document).ready(function(){
jQuery("#login_form1").submit(function(e){
e.preventDefault();
var formData = jQuery(this).serialize();
$.ajax({
type: "POST",
url: "login.php",
data: formData,
success: function(html){
if(html=='true_teacher')
{
$.jGrowl("Loading File Please Wait......", { sticky: true });
$.jGrowl("Welcome to Soch College's E- Learning Management System", { header: 'Access Granted' });
var delay = 1000;
setTimeout(function(){ window.location = 'dasboard_teacher.php'  }, delay);  
}else if (html == 'true'){
$.jGrowl("Welcome to Soch College's E- Learning Management System", { header: 'Access Granted' });
var delay = 1000;
setTimeout(function(){ window.location = 'student_notification.php'  }, delay); 
}else
{
$.jGrowl("Please Check your username and Password", { header: 'Login Failed' });
}
}
});
return false;
});
});
</script>

login.php

<?php include('admin/dbcon.php');
session_start();
$username = $_POST['username'];
$password = $_POST['password'];
//for student login
$query_student = mysql_query("SELECT * FROM student WHERE username='$username' AND password='$password'");
$count_stu = mysql_num_rows($query_student);
$row_stu = mysql_fetch_array($query_student);
//for teacher login
$query_teacher = mysql_query("SELECT * FROM teacher WHERE username='$username' AND password='$password'")or die(mysql_error());
$count_tea = mysql_num_rows($query_teacher);
$row_tea = mysql_fetch_array($query_teacher);
if( $count_stu > 0 ) { 
$_SESSION['id']=$row_student['student_id'];
echo 'true';
}else if( $count_tea > 0 ) { 
$_SESSION['id']=$row_teacher['teacher_id'];
echo 'true_teacher';
}
else{ 
}?>

Does Django ManyToManyField create table with a redundant index?

If I have a model Foo that has a simple M2M field to model Bar:

class Foo(Model):
    bar = ManyToManyField(Bar)

Django seems to create a table foo_bar which has the following indices:

index 1: primary, unique (id)
index 2: unique (foo_id, bar_id)
index 3: non_unique (foo_id)
index 4: non_unique (bar_id)

I recall from my basic knowledge of SQL, that if a query needs to look for conditions on foo_id, index 2 would suffice (since the left-most column can be used for lookup). index 3 seems to be redundant.

Am I correct to assume that index 3 does indeed take up index space while offering no benefit? That I'm better off using a through table and manually create a unique index on (foo_id, bar_id), and optionally, another index on (bar_id) if needed?

Are 'rajivratn'@'%' and rajivratn@localhost refer to same user in MySQL?

I want load data into a table (dataset) of a database. I am using the standard command

 LOAD DATA INFILE '/home/rajivratn/single_output.tsv' IGNORE INTO TABLE dataset ...

I am getting the following permission error:

ERROR 1045 (28000): Access denied for user 'rajivratn'@'localhost' (using password: YES)

Most of the post suggested that this problem is due to FILE privilege on MySQL and can be fixed by the following GRANT command:

GRANT FILE ON *.* to 'rajivratn'@'%';

I have checked the permission and found the following:

mysql> show grants for 'rajivratn'@'%'
    -> ;
+--------------------------------------+
| Grants for rajivratn@%               |
+--------------------------------------+
| GRANT FILE ON *.* TO 'rajivratn'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for rajivratn@localhost                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rajivratn'@'localhost' IDENTIFIED BY PASSWORD 'somepassword'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `yahoo`.* TO 'rajivratn'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

However, I am still getting the ERROR 1045 (28000): Access denied for user 'rajivratn'@'localhost' (using password: YES)

Moreover, Why I can not see the FILE permission in Grants for rajivratn@localhost and why it is different from the grants of 'rajivratn'@'%'

Any suggestions to fix this issue?

Thanks

Limit related records in polymorphic many-to-many realtionship with Laravel

I am using Laravel 5.1 and I need to limit the number of related records I am pulling using a polymorphic many-to-many relationship.

What I would like to do is get a list of categories by parent_id. For each category then I'd like to only pull four posts.

I have have this working with the code below, but it results in a bunch of extra queries. I'd like to just hit the database once if possible. I'd like to use the Laravel/Eloquent framework if at all possible, but am open to whatever works at this point.

@foreach ($categories as $category)
  @if ($category->posts->count() > 0)
    <h2>{{ $category->name }}</h2>
    <a href="/style/{{ $category->slug }}">See more</a>

    {-- This part is the wonky part --}

    @foreach ($category->posts()->take(4)->get() as $post)

      {{ $post->body }}

    @endforeach

  @endif
@endforeach

PostsController

public function index(Category $category)
{
  $categories = $category->with('posts')
      ->whereParentId(2)
      ->get();

  return view('posts.index')->with(compact('categories'));
}

Database

posts
    id - integer
    body - string

categories
    id - integer
    name - string
    parent_id - integer

categorizables
    category_id - integer
    categorizable_id - integer
    categorizable_type - string

Post Model

<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
    public function categories()
    {
        return $this->morphToMany('App\Category', 'categorizable');
    }

Category Model

<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Category extends Model
{
    public function category()
    {
        return $this->belongsTo('App\Category', 'parent_id');
    }
    public function subcategories()
    {
        return $this->hasMany('App\Category', 'parent_id')->orderBy('order', 'asc');
    }
    public function posts()
    {
        return $this->morphedByMany('App\Post', 'categorizable');
    }

I have seen a number of links to this on the web, but nothing that has actually worked for me.

I have tried this solution without any luck.

$categories = $category->with('posts')
->whereParentId(2)
->posts()
->take(4)
->get();

I have looked into this solution by Jarek at SoftOnTheSofa, but it is for a hasMany relationship and to be honest is a bit beyond my sql skill for me to adapt it for my needs.