MySQL sales daily report

Hello guys I'm creating a daily report. I'm a beginner on my MySQL.
I want to generate this kind of data.
I have a table in my phpmyadmin with this structure

<table>
    <tr> 
        <th>ID</th>
        <th>Item_desc<th>
        <th>Total_price<th>
        <th>Quantity</th>
        <th>Date</th>
        <th>Branch</th>
    </tr>

    <tr> 
        <td>1</td>
        <td>Bottle</td>
        <td>100 </td>
        <td>10 </td>
        <td>June 26,2016 </td>
        <td>Rizal</td>
    </tr>

    <tr> 
        <td>2</td>
        <td>Bottle</td>
        <td>120 </td>
        <td>12 </td>
        <td>June 26,2016 </td>
        <td>Rizal</td>
    </tr>

    <tr> 
        <td>3</td>
        <td>Bottle</td>
        <td>100 </td>
        <td>10 </td>
        <td>June 26,2016 </td>
        <td>Rizal</td>
    </tr>
</table>

But I'm getting only 1 record. I want to get all of that records with the sum of total_price and quantity. This is my current query. Thank you.

$query= $this->db->select('*,SUM(total_price),SUM(quantity)')->from('sales')->where('branch',$_SESSION['branch'])->where('date',$from)->get();

Ways to trace a mysql query - mysql_query

Well, it has been days I am trying to fix an issue in one of my webapps using PHP with no luck so far:

Problem: The issue is occurring randomly and cannot be replicated at our will. I was able to find out the specific mysql query that is causing a change(this change is creating the issue) using mysql trigger. I need to find out from where(which line in PHP) this query is executing.

What I did: I checked all the files in the project to see the specific query and there are multiple instances. So I added email function on all these instances and waited for the issue to replicate. At last the issue replicated but I didn't get any email. I confirmed that the email sending code is working. So it seems I cannot trace it from project files.

What I like to do next: See if we can trace(preferably php debug_backtrace()) this specific query.(May be ways to attach to the function mysql_query.)

Other info:

1. This webapp is handed over to me a month ago and I do not know the entire logic of the project
2. I cannot change the way the project use mysql as it is practically impossible because of huge number of files in the project.
3. I am checking for sql injection, malicious code and suspicious POST requests to see if there are any foul play.

My QUESTION is: Is there a way to trace the php function mysql_query like attaching another function after or before execution of mysql_query?

Display the item based on the first item in JComboBox

In GUI, I have two JComboBox where all the comboBox items are retrieved from two tables, which are title and time. In my current code, when the title is selected, displayDate(selected); will get called since I have implemented addActionListener in Select comboBox.

But this is not what I want. When this file is run, I want it straight away display the date based on the first item in Select JcomboBox. When the Select comboBox item changed, only the date changed. What would be the correct way to write?

  public class BuyTicket {  
    static JFrame frame;
    JLabel title,lblMainDate,selectMovie,dateOfShow;
    JComboBox Select,Date;

    public JPanel createContentPane() throws IOException
    {

        title = new JLabel("CYBER CINEMA");
        Select = new JComboBox();
        Select.setLocation(115,90);
        Select.setSize(175, 20);
        try {   
            DatabaseConnection db=new DatabaseConnection();
            Connection connect=db.getConnection();
            String sql="Select title FROM movie";
            PreparedStatement ps=connect.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
            String name = rs.getString("title");
            Select.addItem(name);         
        }

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


        Select.addActionListener(new ActionListener()
        {
    public void actionPerformed(ActionEvent event)
        {
         JComboBox comboBox=(JComboBox) event.getSource();
         Object selected = Select.getSelectedItem();
         displayDate(selected);
        }

    private void displayDate(Object selected) {
        // TODO Auto-generated method stub
          try { 
                Date.removeAllItems();
                DatabaseConnection db=new DatabaseConnection();
                Connection connect=db.getConnection();
                String sql="Select date FROM movie WHERE title = ?";
                PreparedStatement ps=connect.prepareStatement(sql);
                ps.setObject(1, selected);
                ResultSet rs = ps.executeQuery();
                while (rs.next()) {
                String date1 = rs.getString("date");
                DefaultComboBoxModel model = (DefaultComboBoxModel)Date.getModel();
                if (model.getIndexOf(date1) == -1)
                {
                    Date.addItem(date1);
                }

            }

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


    }
        });
    }

Nodejs (Express) connecting MySQL - Local and Remote connection different?

guys. I am learning how to use Express to connect to remote MySQL. So, I started out doing it on my local machine (a local MySQL server). After I have succeeded on the local environment, I tried changing the the connection to a remote MySQL hosting (at DB4Free). Yes, I have succeeded on the localhost. However, whenever I run a Get/Post to the remote MySQL Server, my console show me the error below. I'll attach the related segment of codes below here. I have been trying it the whole afternoon. Hope that someone here can enlighten on this matter. Thank you in advance guys :)

This is the error shown in my console

My file for connecting db is as below - ConnectionString.js

var mysql = require("mysql");

var pool = mysql.createPool({
        connectionLimit : 100,
        host     : '85.10.205.173:3306',
        user     : '******* ',
        password : '*******',
        database : '*******',
    });

exports.getConnection = function(callback) {
  pool.getConnection(function(err, conn) {
    if(err) {
      return callback(err);
    }
    callback(err, conn);
  });
};

Portion of my file for the routes and query is this

var express = require('express');
var router = express.Router();
var mysql      = require('mysql');
var conn = require('../database/ConnectionString');

var result;

//Validate user login
router.get('/login', function(req, res, next) {

        conn.getConnection(
            function (err, client) {

                client.query('SELECT * FROM mt_User', function(err, rows) {
                    // And done with the connection.
                    if(err){
                        console.log('Query Error');
                    }

                    res.json(rows);
                    client.release();

                    // Don't use the connection here, it has been returned to the pool.
                });

        });     

});

PHP exec() not working...Can't find permission error

I am trying to make a simple web application that utilizes MySQL, PHP, and python on my mac. Basically what I am trying to do is run a python script that creates a gzip formatted file from some information in my database (a mysqldump). The script runs fine in the terminal and produces the file correctly, it's just when I run the shell_exec() or exec() it doesn't seem to work. Here is what the code looks so far:

$a = shell_exec("/usr/bin/python test.py 2>&1 &");

when I echo back this execution I get the following error message:

sh: mysqldump: command not found sh: file_2016-06-26-06:32.gz: Permission denied -- file_2016-06-26-06:32.gz

however whenever I echo commands such as "ls", "/usr/bin/python --version", or "pwd", it seems to work. Likewise when I run this command in terminal through php -

  php -r 'echo shell_exec("/usr/bin/python test.py 2>&1 &");'

It runs the script and produces the file fine. I have also tried adding "php-cli" to beginning and removing "2>&1 &" but it just doesn't seem to work to also.

Any ideas here? I would assume it would be a permissions error but all my PHP scripts and python scripts are read and write to everyone.

The python code is:

  import ConfigParser
  import os
  import time
  import getpass

  def get_dump():

user = 'root'
password = 'password'
host = 'localhost'
database = 'dogs'


filestamp = time.strftime('%Y-%m-%d-%I:%M')
os.popen("mysqldump -u %s -p%s -h %s -e --opt -c %s | gzip -c > %s.gz" % (user,password,host,database,database+"_"+filestamp))

 if __name__=="__main__":
   get_dump()

How to upload multiple images with php and sql

I can upload one image at a time with my code, however it I have added multiple="multiple" and it lets me highlight more than one image to upload but only one image gets uploaded to the sql database.

Here is my form below -

<form action="upload_image.php" method="post" enctype="multipart/form-data">
    <p>Select files:<br /><input type="file" name="image[]" multiple="multiple"/></p>
    <p>
    Choose an album<br />
    <select name="album_id">
        <?php
        foreach ($albums as $album) {
                echo '<option value="', $album['id'], '">', $album['name'], '</option>';

        }
        ?>

    </select>
</p>
<p><input type="submit" value="Upload" /></p>
</form>

and my PHP side looks like this -

<?php
if (isset($_FILES['image'], $_POST['album_id'])) {
$image_name = $_FILES['image']['name'];
$image_size = $_FILES['image']['size'];
$image_temp = $_FILES['image']['tmp_name'];
$image_dimension = $_FILES['image']['dimension'];


$allowed_ext = array('jpg', 'jpeg', 'png', 'gif');
$image_ext = strtolower(end(explode('.', $image_name)));
$album_id = $_POST['album_id'];

$errors = array();

    if (empty($image_name) || empty($album_id)) {
        $errors[] = 'Something is missing';
    } else {

        if (in_array($image_ext, $allowed_ext) === false) {
            $errors[] = 'File type not allowed';
        }

        if($image_size > 10485760){
            $errors[] = 'Maximum file size is 10MB'; 
        }

        if (album_check($album_id) === false) {
            $errors[] = 'Couldn't upload to that album';

        }
    }

if (!empty($errors)) {
    foreach ($errors as $error) {
        echo $error, '<br />';
    }

} else {
    upload_image($image_temp, $image_ext, $album_id);
    header('Location: view_album.php?album_id='.$album_id);
    exit();
    }

}

Any help on this would be amazing!

Regards Aaron

MySQL Query INNER JOIN with aliases

I have two tables: users and users_info

users looks like this:

+----+----------+-------+
| id | slug     | name  |
+----+----------+-------+
|  1 | theploki | Kris  |
+----+----------+-------+

and users_info looks like this:

+----+--------+----------+---------------+
| id | parent | info_key | info_val      |
+----+--------+----------+---------------+
|  1 | 1      | email    | kris@kris.com |
+----+--------+----------+---------------+
|  2 | 1      | age      | 28            |
+----+--------+----------+---------------+

I want to SELECT a user who has user_info email = 'kris@kris.com'
- and -
return ALL user_info values and users values

Here's the result I'm looking for:

+----+----------+-------+---------------+-----+
| id | slug     | name  | email         | age |
+----+----------+-------+---------------+-----+
|  1 | theploki | Kris  | kris@kris.com | 28  |
+----+----------+-------+---------------+-----+

So far the closest I've gotten is with this query:

SELECT users.*, users_info.* FROM users
INNER JOIN users_info on users_info.parent = users.id
where users.id = (SELECT users_info.parent FROM users_info
    WHERE users_info.parent = users.id
    AND users_info.info_val = 'kris@kris.com')

And it returns this result:

+----+----------+-------+----+--------+----------+---------------+
| id | slug     | name  | id | parent | info_key | info_val      |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  1 |  1     | email    | kris@kris.com |
+----+----------+-------+----+--------+----------+---------------+
|  1 | theploki | Kris  |  2 |  1     | age      | 28            |
+----+----------+-------+----+--------+----------+---------------+

Obviously I don't need the id of the users_info result and I want each info_key to be the "alias" (or column name) and each info_val to be the value for that "alias".