How to log mysql queries of specific database - Linux

I have been looking at this post How can I log "show processlist" when there are more than n queries?

it is working fine by running this command

mysql -uroot -e "show full processlist" | tee plist-$date.log | wc -l

the problem it is overriding the file

i also want to run it in cronjob. i have added this command to the /var/spool/cron/root

* * * * * [ $(mysql -uroot -e "show full processlist" | tee plist-`date +%F-%H-%M`.log | wc -l) -lt 51 ] && rm plist-`date +%F-%H-%M`.log

but it is not working. or maybe it is saving the log file some place out of the root folder.

so my question is: How to temporary log all queries from specific database and specific table and save the whole queries in 1 file?

note. it is not slow/long quries log iam looking for, but just temp solution to read which queries are running for a database

Delete with a SUbquery MySQL

I'm trying to delele with a subquery that is the same table 'carretilla' This works in SQL Server

DELETE FROM carretilla WHERE carcod IN (SELECT carcod  FROM carretilla WHERE TIMESTAMPDIFF(MINUTE,carfch, NOW()) > 10 group BY carcod);

How can i do it? Thanks

Why BLToolkit inserts \0 instead of '@' symbol in generated queries?

I use BLToolkit with mysql and when I try to insert a record to a table, I am getting a query like this:

INSERT INTO `P`
(
    `Name`
)
VALUES
(
    \0Name
); 

As you can see, this not the best mysql query.

Classes:

public class P
{
    [PrimaryKey]
    [Identity]
    public int? ID  { get; set; }
    public string Name  { get; set; }
}

The code for inserting:

var p = new P();
p.Name = "asdf";
p.ID = (int) db.InsertWithIdentity(p);

Do you know, what is going on?

I'm the "data manager" for a small data-heavy business. What's a more official name for my role, and how can I find specific educational resources?

I'm in charge of the data for a small business. We have a large and growing body of data (customers, orders, suppliers, products, business rules, pricing structures, etc.), and my primary responsibility is to impose and maintain order over the chaos. Although I wear many hats as the resident nerd, my core job duty is to manage our ever-evolving data. To accomplish this, I spend most of day in MySQL, Excel, and Access.

Speaking both broadly and specifically, what is what I'm doing called? Is this rudimentary data quality assurance? Or data quality control? Or data integrity? Or information governance? Database management? Database administration?

I want to institute standards that are as robust as possible in the small business environment, and I'd like to pursue professional educational resources. But the vast majority of generic data/information resources I find are geared towards "big data", which is not my domain, and I'm struggling to find resources that specifically apply to my role when I'm not even sure what it's called.

Locked out of MySQL Database

I am working on a MySQL database that I eventually plan to add an API to. Today, I had a random problem with my regular (non-root) account. When I tried to login through PHPMyAdmin, it said Cannot log in to the MySQL server. So I tried to log in from the command line using mysql -umkaryadi -p and entered my password. I got back Access denied for user 'mkaryadi'@'localhost' (using password: YES). No big deal, I thought, so I logged into PHPMyAdmin and deleted and recreated my mkaryadi account.

Later, I was trying to login to with PHPMyAdmin with my root account and got the same Cannot log in to the MySQL server error. I tried to run mysqld_safe --skip-grant-tables and that let me log in. Then, I attempted to run UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root'; but I got an error to the effect that it couldn't find the Password column in the user table. I had read that you needed to run FLUSH PRIVILEGES; so I tried that before trying to change the password and got: You are logged in as an anonymous user, and anonymous users can't change passwords.

At this point, I tried to back up the databases with mysqldump but now that yields mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect. Now, mysqld_safe --skip-grant-tables doesn't even let me login. I'm not entirely sure what to do here, I've reinstalled MySQL already.

Any help would be appreciated.

Computer: MacBook (13-inch, Aluminum, Late 2008) running OS X 10.10.2

MySQL version: 5.5.45

EDIT: I talked with @SteJ here and still have problems. I am still getting the The server quit without updating PID file error, but have done a fresh install of MySQL.

SELECT from table join

I have 3 tables:

users
  user_id
  first_name
forum_post
 post_id
 post_title
user_post_join
  id
  user_id
  post_id

The join table takes the user_id from users and post_id from forum_posts.

What I am trying to display is a list of posts from the forum_post table and the user who posted it. I am struggling to know where the join should be, here is my attempt so far...

function build_forum_posts(){

        global $dbc;

        $q = "SELECT users.user_id, forum_post.post_id, user_post_join.id 
            FROM users 
            INNER JOIN user_post_join ON users.user_id = forum_posts.post_id
             ";  

        $r = mysqli_query ($dbc, $q); // Run the query.

        // FETCH AND PRINT ALL THE RECORDS
        while ($row = mysqli_fetch_array($r)) {

        echo '
        <div class="post">
            <div class="col-group-2">
                <h3>'.$row["post_id"]. '</h3>
                <p>By: '.$row["user_id"]. '</p>
            </div>
            <div class="col-group-2">
                <div class="post_count">
                    <h3  class="answer">0</h3>
                    <p class="answer">Answers</p>
                </div>
            </div>  
        </div>
        ';

        } 

New To MySql Nested Query

I have 2 tables that I'm trying to pull data from:

• USER
• USER_NAME

USER has the following indexes:

• ID
• ORG_ID
• DEFAULT_EMAIL_ID
• STATUS
• NAME
• CREATED
• UPDATED

USER_NAME has the following indexes:

• ID
• USER_ID
• STATUS
• TIMEZONE_ID
• DST
• LANG
• USERNAME
• PASSWD
• BACKEND
• REGISTERED

My goal is to get USERNAME from USER_NAME and NAME from USER. My background is more in DB2. I'm just learning MySQL. I tried the following with no luck.

select NAME from OST_USER where ID in (select ID, USERNAME from OST_USER_ACCOUNT where CREATED < '2015-07-09');

Any idea what I can do to get the info? Is it even possible with the given indexes? Any help is really appreciated!

Laravel use eloquent to fetch the first value on a column name

Hi I'm using Laravel and to build a task board. I have several models Project, Task, Status,AssignedStatus,TaskProgress. A basic table structure is below:

Project (table name: projects)

id|name|created_at|updated_at

Task (table name: tasks)

id|name|effort|created_at|updated_at

TaskProgress (table name: task_progresses)

id|project_id|task_id|assigned_status_id|created_at|updated_at

Status (order is assigned in this table as a user can choose a set of global or assign there own to a project)

id|name|order|created_at|updated_at

AssignedStatus (table name: assigned_stauses)

id|project_id|status_id|order|created_at|updated_at

Now to build the task board I need to know what the assigned statuses are for the project in question and then grab all of the task_progresses but I only want to grab the latest task_id on the task board so I can see where it is in the cycle. As it could have moved from open to in progress to review to open again as there may be something wrong with the task. i.e. I want to fetch the first task_id but I still want to be able to retrieve all of the tasks within a given status.

To do this I am doing the following:

AssignedStatus::where('project_id','1')->with(['status','taskprogress'])->orderBy('order','asc')->get();

This is my AssignedStatus model:

public function status() {
    return $this->belongsTo('Status', 'status_id');
}

public function taskprogress() {
    return $this->hasMany('TaskProgress', 'assigned_status_id');
}

However when I use the taskprogress function I get multiple task_id's which i don't want, I want to know what status it is in at the current moment in time

I'm not sure how I should structure why query to do this, does anyone have any solutions to the problem that they could perhaps share with me? Sorry bit of a newbie here. Can this be done with Eloquent? or do I need to loop through the nested objects and unset taskprogress by the created_at date? Hope somebody can help.

A var_dump is available here in my dropbox

Example data is below, i have timestamps in all tables, created_at and updated_at but not displayed below:

task_progresses table

id | user_id | project_id | task_id | assigned_status_id
1  | 2       | 3          |  1      |    4
2  | 3       | 3          |  2      |    3
3  | 3       | 3          |  2      |    2
4  | 2       | 3          |  2      |    1
5  | 1       | 3          |  2      |    4
6  | 2       | 3          |  2      |    3
8  | 2       | 3          |  1      |    2
8  | 2       | 3          |  1      |    1

assigned_statuses

id | project_id | status_id 
1  | 2          | 1
2  | 2          | 3
3  | 2          | 4
4  | 2          | 2

statuses

id | name        |order |global
1  | Backlog     |0     | 1    
2  | Closed      |4     | 1
3  | In Progress |2     | 1
4  | Complete    |5     | 1
5  | Done        |3     | 0
6  | Open        |1     | 0

XAMPP - how to expand a category list imported from MySQL

I have imported a set of different values from two different tables in phpMyAdmin, one from Category table (where cat_id is a PK) and another from Item table; where (cat_id is a FK).

<?php
require ('config.php');

$que = "SELECT * FROM category";
$run = mysql_query($que);
$j = 0;
while($row = mysql_fetch_array($run))
{
    $cat_idd[$j] = $row['cat_id'];
    $cat_namee[$j] = $row['cat_name'];
    $j++;
}
for($i = 0;  $i < count($cat_idd);  $i++)
{
    $que2 = "SELECT * FROM item WHERE cat_id = '$cat_idd[$i]' ";
    $result = mysql_query($que2);
    $run = mysql_num_rows($result);
    echo "<a href=''>".$cat_namee[$i]."(".$run.")</a><br>";
}
?>

Here is what I have so far:

Now how do I expand it? or for example, how do I show the two items that are stored in the category named Clothing on the same page or next? Since this code only helps me to view the number of items stored inside the database,

Here is my form:

    <form name = "view" method = "POST" action ="cart.php">
      <table align = 'center' width = '100%' border = '4'>
      <tr bgcolor = 'yellow'>
      <td colspan = '20' align = 'center'><h2> Viewing all the Products </td>
</tr>
      <tr align = 'center'>
      <th>Item ID</th>
      <th>Name</th>
      <th>Price</th>
      <th>Select</th>
      </tr>

      <tr  class = "odd">


      <?php
        require ('config.php');

        $que = "SELECT * FROM category";
        $run = mysql_query($que);
        $j = 0;
      while($row = mysql_fetch_array($run))
       {
         $cat_idd[$j] = $row['cat_id'];
         $cat_namee[$j] = $row['cat_name'];
         $j++;
       }

I thought of using an array function to store the value inside a category i.e Clothing(2) => 2.

       function array_insert(&$array, $position, $insert)
       {
         if (is_int($position)) {
          array_splice($array, $position, 0, $insert);
       } else {
          $pos   = array_search($position, array_keys($array));
          $array = array_merge(
             array_slice($array, 0, $pos),
             $insert,
            array_slice($array, $pos)
        );
       }
       }

      $arr = array();
      $arr[] = 2;


      for($i = 0;  $i < count($cat_idd);  $i++)
      {
       $que2 =  "SELECT * FROM item WHERE cat_id = '$cat_idd[$i]'";
       $result = mysql_query($que2);
       $run = mysql_num_rows($result);
       echo "<a href=''>".$cat_namee[$i]."(".$run.")</a><br>";
     array_insert($arr, 0, "$run");
     // echo $arr[0];
      }
        $que2 = "SELECT * FROM item WHERE cat_id = '1'";      //instead of using '1' i wish to use the one user clicks on!
        $res2 = mysql_query($que2);
       while($row = mysql_fetch_array($res2))
        {
          $i_id = $row['item_id'];
          $i_namee = $row['item_name'];
          $i_price = $row['item_price'];


       ?>
       <td><?php echo $i_id; ?></td>
       <td><?php echo $i_namee; ?></td>
       <td><?php echo $i_price; ?></td>
       <td><input type="checkbox" name="addcart" value="<?php echo $item; ?>" onClick="return KeepCount()" />Tick</td>
</tr>
       <?php }  ?>    
       <br><br>
       </table>
       <input type = "hidden" name = "check">
       <button type=  "submit" onclick = "location.href = 'cart.php';" id = "cart" style="float:right; margin-top: 30px; margin-right: 10px;">Add to Cart</button>

The above image is the result what I want, this one is specific for Clothing(2) only. I want it to change as the user clicks on something else for example, Shoes(1).

Django unique_together but only for filtering

Is there a way to filter a django model, where two fields are unique together?

For example, I have this model:

class Sequence(models.Model):
    id       = models.CharField(max_length=25, primary_key=True)
    taxonomy = models.CharField(max_length=25)
    sequence = models.TextField()

There can be multiple Sequence objects that have the same the sequence and taxonomy. I want to have a unique subset of Sequence objects where no taxonomy has multiple sequences that are identical, and only pick one object if there are multiple.

So far I have tried iterating over the results:

def unique(query_set):
    used_taxa = {}
    for seq in query_set.all():
        if not seq.sequence in used_taxa:
            used_taxa[seq.sequence] = [seq.taxonomy]
            yield seq
        elif seq.sequence in used_taxa and not seq.taxonomy in used_taxa[seq.sequence]:
            used_taxa[seq.sequence].append(seq.taxonomy)
            yield seq
        else:
            pass

This gets me the correct result, but I need the overall count becuase I am doing pagination later on.

This also gets me closer, but I don't have access to the full Sequence object after values has been called:

result = Sequence.objects.values("sequence", "taxonomy").annotate(id=Max("id"))

If someone can point me in the right direction, I would really appreciate it! Thanks.

PDO how to check if input empty to not use WHERE

Hello I would like how I can transform the following code to use BindValues and to do the same job.

$sql = "SELECT * FROM documents WHERE 1";
            if (!empty($topic))   { $sql .= " AND topic = '$topic' ";   }
            if (!empty($date ))   { $sql .= " AND date LIKE '%$date%' "; }

WHERE clause to be applied only on the filled input So my question is how to transform this code in to the the following query

$sql = new page($pages "SELECT * FROM table WHERE topic LIKE :topic OR date LIKE :date ", $option);

$sql ->BindValue(':topic ', $_POST['topic '], PDO::PARAM_STR);
$sql ->BindValue(':date ', $_POST['date  '], PDO::PARAM_STR);

selecting and ordering from 3 different tables in mysql

I have a code which selects 10 of the users who have "earned" money ordering by the price desc. I have been using this query

SELECT products.seller, 
SUM(products.price * (1 - reseller.fee / 100)), 
COUNT(*) 
FROM products
INNER JOIN reseller ON reseller.username = products.seller
WHERE (products.seller!= 'MYSITE') 
AND products.sold=1 
AND products.sellerpaid=0 
AND products.username != 'None' 
GROUP BY products.seller
ORDER BY SUM(products.price * (1 - reseller.fee / 100)) DESC 
LIMIT 10

By this i get:

uploaded_by  SUM()  COUNT()
 username    10.00     2
 username1   11.00     3

....

Which works absolutely fine, but now i have another table, products1 and that contains something more thats why i cannot merge them, so I want to get the same results as in the first one but fetching the price of all products from this table as well I have tried LEFT JOIN, RIGHT JOIN, and many others but still cant do it correctly.

Please do not tell me to read more about joins cuz i already have, just cant do it, if you can come up with a solutions then post it if possible.

Also if there is anything that you do not understand, please comment so I can give further explanations.

Thank you.

Is possible to create a mysql database at execution time in node.js

For example, if I have a form with an input field where I type the database name and I click submit button to create a mysql database with the name from the field, is possible?

Upload thumbnail and original image to mySQL AFNetworking

I am using AFNetworking to upload images to a mySQL database via PHP from an iPhone app. Here is the code I am using:

AFHTTPRequestOperationManager *manager = [AFHTTPRequestOperationManager manager];
manager.responseSerializer.acceptableContentTypes = [NSSet setWithObjects:@"text/html",@"application/json",nil];
manager.responseSerializer = [AFHTTPResponseSerializer serializer];
CGFloat compression = 1.0f;
UIImage *image = _uploadImage.image;
NSData *imageData = UIImageJPEGRepresentation(image, compression);
int random = arc4random() % 9999999;
NSString *photoName=[NSString stringWithFormat:@"%d-image.jpg",random];
[manager POST:@"http://ift.tt/1fQm1gD" parameters:parameters constructingBodyWithBlock:^(id<AFMultipartFormData> formData) {
[formData appendPartWithFileData:imageData name:@"image" fileName:photoName mimeType:@"image/jpeg"];
    } success:^(AFHTTPRequestOperation *operation, id responseObject) {            
        NSLog(@"Success: %@", responseObject);
    } failure:^(AFHTTPRequestOperation *operation, NSError *error) {
        NSLog(@"Error: %@", error);
    }];

My question is - is it possible to use AFNetworking to save a thumbnail version as well as saving the original photo? If so, how would I go about this? Or would I have to use another library to achieve this? Will appreciate any help.

Using IFNULL in sqlalchemy core

I'm trying to use sqlalchemy core select rows from a mysql table using IFNULL.

Given a table like so:

id    int1    string1   other
1      7        NULL    other stuff
2      NULL     bar     more stuff 

The sql would be something like:

SELECT IFNULL(int1, 0) AS int1, IFNULL(string1, '') AS string1 FROM table

Is this possible using the core? What would be great would be something like

s = select(ifnull(table.c.int1, 0), ifnull(table.c.string1, ''))

After importing MySQL db I can no longer log in to phpMyAdmin

Not paying attention, I exported a database from a MySQL 5.5 server, and imported it into a MySQL 5.1 server. Both via phpMyAdmin.

No errors were reported in any logs. I was also immediately able to browse the database as well.

No other changes have been made to the servers or the software. However, following that event, I am unable to log into MySQL via phpMyAdmin using any available login.

I am able to log in via console, and have dropped the added database with no effect. I have also updated the max_allowed_packet size to 16M per other recommended steps also to no avail.

When I try logging in, I get the following message: "#2006 Cannot log in to the MySQL server".

I have not been able to locate any additional information regarding fixes for this. Any recommendations?

Options currently under consideration:

1. Upgrade phpMyAdmin
2. Upgrade MySQL (needed as well as required for item 1)

How to check if query returned true or false in replacement for mysql_error() in PDO

I am creating a web based application, and I am using PDO for my database. I have a query that selects everything from login table where username=something and password=something.

My code:

$query = $db->prepare("SELECT * FROM login WHERE username=:username AND password=:password");
$query->bindParam(':username',$username);
$query->bindParam(':password',$password); 
$query->execute();

However I want to check if the query returned true or false. For example in mysql we used to say:

$query = mysql_query("SELECT * FROM login WHERE username='$username' AND password='$password'  "); 
if($query == false){
    die(mysql_error()); 
}

My question is, how do I check if the query returned false or true using PDO and gives an error? This will help me get errors on my code during development.

What am i going to replace with mysql_error()?

How should hierarchical data be structured in mySQL

We are designing a database to keep track of a network including Servers, Switches, and cameras. We have noticed that there is a great deal of inheritance in the table definitions. For example, all switches have an IP address, but some have 48 ports while others only 24, some have SFP ports, some two power supplies, others just one.

I know that whether in normal or OO programming, you prefer to put everything in modules/objects. So it is better to have a class for a DOOR and a HANDLE object, instead of having a DOOR class with variables defining its handle. Is this the same with relational databases.

Here are two ways of describing transcievers. I can either create one table with boolean variables like so

CREATE TABLE IF NOT EXISTS Transceiver
(
   ID INT NOT NULL AUTO_INCREMENT,
   singleMode BOOL,
   multiMode BOOL,
   1Gig BOOL,
   10Gig BOOL,
   1km BOOL,
   10km BOOL,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

Or I can create multiple tables like so

CREATE TABLE IF NOT EXISTS MM1G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS MM10G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS MM1G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS MM10G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM1G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM10G1KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM1G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS SM10G10KmTransceiver
(
   ID INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY (ID)
)ENGINE=InnoDB;

The problem with relational databases is that you have to use Joins and unions, and if you have 15 different tables, the whole things turns into a nightmare.

In case anyone is wondering, we will later need to generate a Bill of Materials, so we may need to find out the total number of single mode transceivers, or total number of PSUs, or power consumption of all 24 port switches, etc.

EDIT It just occoured to me that there is no hierarchy in my example, however, I could make it go like so:

• Transceiver Table
• Single/Multi Mode Transceiver table pointing to Transceiver Table
• etc.

Ironworker connection to Openshift MySQL database

I just installed the ironworker cartridge from Openshift's marketplace - experimenting with a hello-world nodejs worker that tries to connect to the MySQL DB on Openshift and the attempt is refused. [BTW, I can connect to the DB via MySQL workbench using the very same credentials, via port-forwarding]. I also SSH'd in to grab the environment info with

env | grep OPENSHIFT_MYSQL

and used the host and port.

Tried creating a basic connection in the worker and received the following result (actual config values changed):

my config: {"host":"111.11.nnn.nnn","port":3306,"user":"myapp","pass":"xxxxxxxxxxx","db":"app_db"}
about to make DB connection
We got an error connection to the DB: {"code":"ECONNREFUSED","errno":"ECONNREFUSED","syscall":"connect","fatal":true}

I believe the connection is being refused not by the database, but perhaps the firewall/environment itself because I can connect via other means. Suggestions?

FYI - the code:

var mysql = require('mysql');

// variables specific to your ironworker environment
var iron_helper = require('node_helper');
var params = iron_helper.params;
var task_id = iron_helper.task_id;
var config = iron_helper.config;
console.log('my config: ' + JSON.stringify(config));
console.log('about to make DB connection');
var connection = mysql.createConnection('mysql://'+config.user+':'+config.pass+'@'+config.host+':'+config.port+'/'+config.db);


var query = "select count(*) from reservations;";

connection.query( query, function(err, callResults){
if (err) {
   console.log("We got an error connection to the DB: " + JSON.stringify(err));
}
else {
    console.log("We got a result: " + JSON.stringify(callResults));
}
connection.destroy();
});

Selecting the number of last consecutive days from timestamp (excepting today)

I have a table A_DailyLogins with the columns ID (auto increment), Key (userid) and Date (timestamp). I want a query which would return the number of last consecutive days from those timestamp based on the Key, for example if he has a row for yesterday, one for two days ago and another one for three days ago, but the last one isn't from four days ago, it would return 3, because this is the number of last days the user was logged in.

My attempt was to create a query selecting the last 7 rows of the players ordered by Date DESC (this is what I wanted in the first place, but then I thought that it would be great to have all the last consecutive days), and then I retrieved the query result and compared the dates (converted to year/month/day with functions from that language [Pawn]) and increased the number of consecutive days when a date is before the other one with one day. (but this is extremely slow compared to what I think that can be done directly only with MySQL)

The closest thing I found is this: Check for x consecutive days - given timestamps in database . But it still isn't how I want it to be, it's still pretty different. I tried to modify it, but it is way too hard for me, I don't have that much experience in MySQL.

Query runs faster without an index. Why?

I have two tables. One of those tables has this schema:

CREATE TABLE `object_master_70974_` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_object` int(10) unsigned NOT NULL DEFAULT '0',
 `id_master` int(10) unsigned NOT NULL DEFAULT '0',
 `id_slave` int(10) unsigned NOT NULL DEFAULT '0',
 `id_field` bigint(20) unsigned NOT NULL DEFAULT '0',
 `id_slave_field` bigint(20) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_object`    (`id_object`,`id_master`,`id_slave`,`id_field`,`id_slave_field`),
 KEY `id_object_2` (`id_object`,`id_master`,`id_field`,`id_slave_field`),
 KEY `id_object_3` (`id_object`,`id_slave`,`id_field`),
 KEY `id_object_4` (`id_object`,`id_slave_field`),
 KEY `id_object_5` (`id_object`,`id_master`,`id_slave`,`id_field`),
 KEY `id_object_6` (`id_object`,`id_master`,`id_slave`,`id_slave_field`),
 KEY `id_master` (`id_master`,`id_slave_field`),
 KEY `id_object_7` (`id_object`,`id_field`)
) ENGINE=InnoDB AUTO_INCREMENT=17827 DEFAULT CHARSET=utf8;

As you can see, there is an overlapping index KEY id_object_5 (id_object,id_master,id_slave,id_field) and there is no index that would cover these three fields: id_object, id_master, id_field. However, when I run these two queries:

SELECT f1.id 
FROM object_70974_ f1  
LEFT JOIN object_master_70974_ mss0 ON mss0.id_object IN (70974,71759)  
AND mss0.id_master = 71100 AND mss0.id_slave = 70912 AND mss0.id_field = f1.id

and

SELECT f1.id 
FROM object_70974_ f1  
LEFT JOIN object_master_70974_ mss0 ON mss0.id_object IN (70974,71759)  
AND mss0.id_master = 71100 AND mss0.id_field = f1.id

they both return the same number of rows (since in fact id_slave field does not really matter) - 3530, however, the first query is slower than the second query by one second - 8 and 7 seconds respectively. So, I guess I have to ask two questions - 1) why does the second query run faster, even though it does not use index and 2) why does the first query run so slowly and why does not it use an index (obviously). In short, what the heck is going on?

EDIT

This is the result of EXPLAIN command (identical for both queries):

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "f1"    "index" \N  "attr_80420_"   "5" \N  "3340"  "Using index"
"1" "SIMPLE"    "mss0"  "ref"   "id_object,id_object_2,id_object_3,id_object_4,id_object_5,id_object_6,id_master,id_object_7"   "id_master" "4" "const" "3529"  "Using where"

EDIT

It's extremely interesting, because if I DROP id_master index (which for some strange reason is used by both queries), then it starts to use id_object_5 index.

EDIT

And, yes, with id_master index being dropped, both queries start to run super-fast. So, I guess there is some trouble with optimizer.

EDIT

I even have a guess what trouble faces the optimizer - it may be incorrectly treats id_slave_field field name in the key, as if it were two fields instead - id_slave and id_field. In this case it becomes reasonable, why it firstly used this key in both queries.

PDO Suggest refine results different than wildcard symbol

I am trying to make a PDO query to be searchable not only by the whole string but also by first letter or last letter anything like this. My question is what approach I have to take to achieve this goal.

My original idea was to use wildcard symbol and something like the following:

SELECT * FROM idname WHERE field LIKE CONCAT('%', :field , '%')

but this option for me is not working since I am getting an error: Warning: Division by zero in

My code ad the moment is the following:

try
{

$paginate = new pagination($page, 'SELECT * FROM idname WHERE field LIKE :field, $options);


}
catch(paginationException $e)
{
    echo $e;
    exit();
}

$paginate->bindValue(':field', $_POST['field'] , PDO::PARAM_STR);
$paginate->execute();

Any suggestions are welcome ?

how to insert form data into MYSQL using python

i am trying to insert the data entered into the web form into database table,i am passing the data to the function to insert the data,but it was not sucessful below is my code

def addnew_to_database(tid,pid,usid,address,status,phno,email,ord_date,del_date):
    connection = mysql.connector.connect(user='admin_operations', password='raghu',host='127.0.0.1',database='tracking_system')
    try:
        print tid,pid,usid,address,status,phno,email,ord_date,del_date
        cursor = connection.cursor()
        cursor.execute("insert into track_table (tid,pid,usid,address,status,phno,email,ord_date,del_date) values(tid,pid,usid,address,status,phno,email,ord_date,del_date)")
        cursor.execute("insert into user_table (tid,usid) values(tid,usid)")
    finally:
        connection.close()

hotspot billing software in php developer needed with ddwrt firmware or else [on hold]

I was going to share net through wifi at my area, So i need online billing softwre, for when user connect to my wifi and excess any page, Page auto redirect to login page. After Login sucessful user can excess any website.

I need online this program in php. with bandwith controll only.

I will Give Money who help me to create sotware in php. I am also a php dveloper but i dont have an Much Idea to do, This project.

Note: Limit user internet bandwith .

Thank You My Contact number is. +91 8149587579 Email: 0shoaib0@gmail.com

bootstrap formvalidation.io remote validator MySQL PHP

i'm having some issues getting a modal form validate a username from Mysql database using PHP.

here is my PHP script to validate the username, when i run it alone it works but when it will not get called from the remote validator.

PHP code :

checkUsername.php

            <?php

             $isAvailable = true;

             //get the username  and password
               $uname = trim($_POST['username']);
               $umail = trim($_POST['email']);

            //connect to database   
            require_once '/php-includes/dbconfig.inc.php';

                     $stmt = $DB_con->prepare("SELECT username, email FROM member WHERE username=:uname OR email=:umail");
                     $stmt->execute(array(':uname'=>$uname, ':umail'=>$umail));
                     $row=$stmt->fetch(PDO::FETCH_ASSOC);


                        if($row['username']==$uname) {
                            $isAvailable = false; 
                        }

                     // Finally, return a JSON
                echo json_encode(array('valid' => $isAvailable));
            ?>

and this is the formValidation.io script that i'm using from http://ift.tt/1OFKcKf

            $(document).ready(function() {
                $('#registerForm')
                    .formValidation({
                        framework: 'bootstrap',
                        icon: {
                            valid: 'glyphicon glyphicon-ok',
                            invalid: 'glyphicon glyphicon-remove',
                            validating: 'glyphicon glyphicon-refresh'
                        },
                        fields: {
                            userName: {
                                validators: {
                                    notEmpty: {
                                        message: 'The user name is required'
                                    },
                                    remote: {
                                        url: 'checkUsername.php'
                                    }
                                }
                            }
                        }
                    })
                    // This event will be triggered when the field passes given validator
                    .on('success.validator.fv', function(e, data) {
                        // data.field     --> The field name
                        // data.element   --> The field element
                        // data.result    --> The result returned by the validator
                        // data.validator --> The validator name

                        if (data.field === 'userName'
                            && data.validator === 'remote'
                            && (data.result.available === false || data.result.available === 'false'))
                        {
                            // The userName field passes the remote validator
                            data.element                    // Get the field element
                                .closest('.form-group')     // Get the field parent

                                // Add has-warning class
                                .removeClass('has-success')
                                .addClass('has-warning')

                                // Show message
                                .find('small[data-fv-validator="remote"][data-fv-for="userName"]')
                                    .show();
                        }
                    })
                    // This event will be triggered when the field doesn't pass given validator
                    .on('err.validator.fv', function(e, data) {
                        // We need to remove has-warning class
                        // when the field doesn't pass any validator
                        if (data.field === 'userName') {
                            data.element
                                .closest('.form-group')
                                .removeClass('has-warning');
                        }
                    });
            });
            </script>

Inserting values into column from another table in MySQL

I am inserting one column values from one table to another table. I have following two tables.

 Table - a

    ID    Entry_date    weight   height    TagsA
    111   1968-07-31    22       34
    111   1968-12-31    34       37
    112   1969-03-31    8        43
    112   1969-07-31    45       48
    113   1970-09-30    67       94
    113   1973-03-31    23       76

   Table - b

    ID    Entry_date    TagsB
    111   1968-07-31    1
    111   1968-12-31    1
    112   1969-03-31    0
    112   1969-07-31    0
    113   1970-09-30    0
    113   1973-03-31    1

These both tables are having equal number of rows around 44300. ID and Entry_date columns are same for both the tables. I want to get insert all the values present in Table - b column TagsB to Table - a column TagsA. So the resulting table should look like this:

 Table - a

    ID     Entry_date    weight   height    TagsA
    111   1968-07-31    22       34        1
    111   1968-12-31    34       37        1
    112   1969-03-31    8        43        0
    112   1969-07-31    45       48        0
    113   1970-09-30    67       94        0
    113   1973-03-31    23       76        1

I tried to use update:

update a set TagsA = (select TagsB from b where a.ID = b.ID and a.Entry_date = b.Entry_date);

Error Code: 1242. Subquery returns more than 1 row  714.523 sec

How to proceed in this case?

MySql Table Self Join

I have the table below

sku|date
---|---
A1 |Jan
A2 |Jan
A1 |Jan
A1 |Feb
A2 |Feb

I'm trying to get the count per month. I'd like to get the output below;

sku|JAN|FEB
---|---|--|
A1 |2  |1 |
A2 |1  |1 |

I've tried self join and left join with no success, and I'm getting a bit confused.

I get incorrect results with the code below

select s.sku, count(f.sku)
from database f, database s
where f.sku between '2015-01-01' and '2015-01-31'
and
s.sku=f.sku
group by s.sku

Please advise.

Rails active record WHERE EXISTS query

I have an SQL query that returns what I need, but I'm having trouble converting this into an active record query the "Rails way".

My SQL query is:

SELECT * from trips 
WHERE trips.title LIKE "%Thailand%"
AND EXISTS (SELECT * from places WHERE places.trip_id = trips.id AND places.name LIKE "%Bangkok%")
AND EXISTS (SELECT * from places WHERE places.trip_id = trips.id AND places.name LIKE "%Phuket%")

I'm trying something like this using Rails:

@trips=Trip.where("trips.title LIKE ?", "%Thailand%")
@trips=@trips.includes(:places).where(("places.name LIKE ?","%Bangkok%").exists?) => true, ("places.name LIKE ?","%Phuket%").exists?) => true)

But it doesn't seem to work and i'm stumped as to what to try.

MySQL Where clause values 0 and 1 not working correctly

The situation

In table I have columns configurated as ENUM('0','1'). I have select query build with PDO like this example

$value = isset($_POST['value']) ? $_POST['value'] : (isset($_GET["value"]) ? $_GET["value"] : null);

$sql = $pdo->prepare("SELECT * FROM tablename WHERE column = :value");
$sql->bindValue(':value', $_POST['value']); // post contains 0 or 1
$sql->execute();

---

The problem

When printing the results, value 1 is working normally. But when using value 0, all rows are showing including rows with value 1.

Following query is working normally when trying it in HeidiSQL, but it's not with PHP. What's wrong?

SELECT * FROM tablename WHERE column = '0'

I noticed that PHP thinks $_POST['value'] is unset when its value is zero. I'm using isset()

---

Trying to solve the problem

• No effect either if using $_GET['value'] and url like index.php?value=0

• Tried following, not working

  $sql->bindValue(':value', '0'); // post contains 0 or 1
  

• I changed column type to TINYINT(1) - no effect. When looking for zero, all are showing.

• Set PDO bindValue() $data_type to PDO::PARAM_BOOL, not working

MySQL INSERT IGNORE Adding 1 to Non-Indexed column

I'm building a small report in a PHP while loop. The query I'm running inside the while() loop is this:

INSERT IGNORE INTO `tbl_reporting` SET datesubmitted = '2015-05-26', submissiontype = 'email', outcome = 0, totalcount = totalcount+1

I'm expecting the totalcount column to increment every time the query is run. But the number stays at 1. The UNIQUE index composes the first 3 columns.

Here's the Table Schema:

CREATE TABLE `tbl_reporting` (
 `datesubmitted` date NOT NULL,
 `submissiontype` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
 `outcome` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `totalcount` mediumint(5) unsigned NOT NULL DEFAULT '0',
 UNIQUE KEY `datesubmitted` (`datesubmitted`,`submissiontype`,`outcome`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

When I modify the query into a regular UPDATE statement:

UPDATE `tbl_reporting` SET totalcount = totalcount+1 WHERE datesubmitted = '2015-05-26' AND submissiontype = 'email' AND outcome = 1

...it works.

Does INSERT IGNORE not allow adding numbers? Or is my original query malformed?

I'd like to use the INSERT IGNORE, otherwise I'll have to query for the original record first, then insert, then eventually update.

My PHP code on my PC(working great) doesn't work on my laptop

I have these two functions to fill a dropdown list.

function fillcombo($table,$colname)
{

$list="";

$sql="select $colname from $table order by $colname";

$Rs = setRs($sql); // Get data from database using setRs function


    foreach($Rs as $opt) {

    $list .='<option value="'.$opt[$colname] .'">'. $opt[$colname] .'</option>';

    }
    unset($opt);
 return $list;
}

//---------------------------------------------------------------------------

function setRs($sql){

include ('connect.php');

mysqli_select_db($conn,"MyDB" ); //Select database

$Rs = mysqli_query($conn,$sql) or die(mysqli_error($conn)); //Run Query

return $Rs; //Return recordset

$conn->close();

}


// This $Recordset has 93 rows as in my database. But the foreach loops only 5 times.

return $list yields the following html.

<option value=""></option><option value=""></option><option value=""></option><option value=""></option><option value=""></option>

On my PC it works fine and the select list is filled with all the 93 values. Any help will be greatly appreciated..Thanks

Cannot add foreign key constraint. Mysql

By executing the following SQL statement it gives me an error such as "Error 1215: Cannot add foreign key constraint"

this is my SQL code. the erro gives me to create the first table: "process"

CREATE TABLE process(
  idp VARCHAR(36) NOT NULL,
  idc VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  provider TEXT NULL,
  PRIMARY KEY(idp),
  FOREIGN KEY (idc) REFERENCES clients(idc)
  ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE tag_group (
  idtg VARCHAR(36) NOT NULL,
  idp VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  ttl TEXT NULL,
  PRIMARY KEY(idtg),
  FOREIGN KEY (idp) REFERENCES process(idp)
  ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE clients (
  idc VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  company VARCHAR(255) NOT NULL,
  address VARCHAR(255) NULL,
  phone VARCHAR(45) NULL,
  fax VARCHAR(255) NULL,
  website VARCHAR(255) NULL,
  PRIMARY KEY(idc)
) ENGINE=INNODB;

CREATE TABLE users (
  idu VARCHAR(36) NOT NULL,
  idc VARCHAR(36) NOT NULL,
  title VARCHAR(36) NULL,
  firs_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  password VARCHAR(255) NOT NULL,
  country VARCHAR(255) NULL,
  city VARCHAR(255) NULL,
  telephone VARCHAR(255) NULL,
  is_agent BOOLEAN NOT NULL,
  last_login TIMESTAMP NULL,
  timeout TIMESTAMP NULL,
  timeout_enabled BOOLEAN NULL,
  preferred_language VARCHAR(36) NOT NULL,
  PRIMARY KEY(idu),
  FOREIGN KEY (idc) REFERENCES clients(idc)
  ON DELETE CASCADE
) ENGINE=INNODB;

Mysql regex error #1139 using literal -

I tried running this query:

SELECT column FROM table WHERE column REGEXP '[^A-Za-z\-\']'

but this returns

#1139 - Got error 'invalid character range' from regexp

which seems to me like the - in the character class is not being escaped, and instead read as an invalid range. Is there some other way that it's suppose to be escaped for mysql to be the literal -?

This regex works as expected outside of mysql, http://ift.tt/1Iz37Gq.

I came up with an alternative to that regex which is

SELECT column FROM table WHERE column NOT REGEXP '([:alpha:]|-|\')'

so the question isn't how do I get this to work. The question is why doesn't the first regex work?

Nodejs user authentication sql command

i am trying to make user authentication form in node.js with express.

currently i am using this query to insert in my db.

var  username = req.body.username;
var  password = req.body.password;


connection.query('USE one');
  connection.query("INSERT INTO users(id,name,pass) VALUES (1,"+ username +","+password+")", 
    function (err, result) {
        if (err) throw err;
    }
);

my db structure is

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | NO   |     | NULL    |                |
| pass  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

Please help i am getting an error:-

throw err; ^ Error: ER_BAD_FIELD_ERROR: Unknown column 'jjhk' in 'field list'

Parse Google image search parameters and save them in a db

I am trying to save google images in a db with various search parameters (i.e. TBS values). I need an idea how to parse google image search url so that I can save image sizes (i.e. tbs=isz:l for larger images, tbs=isz:m for medium images), image colors (i.e. tbs=ic:color for full color) and image type (i.e. tbs=itp:face for photo) etc.

for example, the following url search for “michael jackson” as a phrase, and limit results to 4 megapixel images or larger, color images, face images, and group the results by topic:

http://ift.tt/1DWymG1

and I would like to save all image info in my db.

I cannot figure out how to parse these search parameters dynamically so as to save in a db.

Here is my db schema:

CREATE TABLE IF NOT EXISTS `images` (
  `image_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `keyword_id` int(11) unsigned NOT NULL,
  `url` varchar(2083) NOT NULL,
  `size` varchar (50) NOT NULL,
  `color` varchar (50) NOT NULL,
  `type` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`image_id`),
  KEY `keyword_id` (`keyword_id`)
) ENGINE=InnoDB; 

PHP-SQL : What is the solution if you want to do a search and use UNION with two tables that have differents columns?

PHP-SQL : What is the solution if you want to do a search (with match and against) and use UNION with two tables that have differents columns (numbers, types, names) because if you use UNION, the columns have to have same name, number and type? All I found was to use NULL and ALIAS to simulate the columns correponding. Is there another solution for this case ?

to accelerate mysql query

I have this query in mySql. This query is taking too long to run, and I know the problem is the selectors (coalesce ((SELECT ...), I do not know how to speed up a query, via join.

I am hoping some of you SQL gurus will be able to help me.

SELECT
    COALESCE(
        (SELECT CONCAT(d.PRIJEVOZNIK, ' ', d.VOZAC_TRANSFER) 
         FROM dokum_zag as d
         where d.SIFKNJ='NP' and
               d.ID_VEZA=dokum_zag.ID and 
               d.korisnicko_ime=dokum_zag.korisnicko_ime 
         ),'') as PRIJEVOZNIK,
      (RELACIJA_TRANS_VOZ_TRANS) as RELACIJA_TRANS_VOZ, 
      (PRIJEVOZNIK_POVRATNI_TRANS) as PRIJEVOZNIK_POVRATNI, 
      (VAUC_KNJIZENO_TRANS) as VAUC_KNJIZENO, 
      ID_NALOGA, 
      ID_NALOGA_POV, 
      ID_VAUCHER, 
      DOLAZAK, VRIJ_TRANSFER,ODLAZAK,VRIJEME_LETA_POVRAT ,BRDOK, NOSITELJ_REZ, RELACIJA_TRANS, VOZILO_NAZIV, BROJ_NALOGA,BROJ_NAL_POV,BROJ_VAUCHER,BROJ_SOBE,VALIZN,PAX, MPIZN,ID 
FROM
    dokum_zag 
WHERE 
    korisnicko_ime = '10' and 
    ((DOLAZAK='2015-07-30') or (ODLAZAK='2015-07-30')) and 
    STORNO <> 'DA' and 
    SIFKNJ = 'TR' and 
   ((YEAR(DOLAZAK)= '2015') or (YEAR(ODLAZAK)= '2015')) 
order by 
   (CASE WHEN DOLAZAK < '2015-07-30' THEN ODLAZAK ELSE DOLAZAK END) , 
   (CASE WHEN DOLAZAK < '2015-07-30' THEN VRIJEME_LETA_POVRAT ELSE VRIJ_TRANSFER END), ID 

Binding Values Inside Pagination PDO

Hello I am using the Pagination Script of http://ift.tt/1GOP7GB

But have a problem with making it prepared statement and binding my values inside the PDO query.

Here is my code of it so far:

try
{
            $paginate = new pagination($page, 'SELECT * FROM database WHERE id_name LIKE :id_name ', $options);

}

Main question here is indeed how to bind the values inside it ?

How to convert mysql query into postgresql

Hey I am trying to change the mysql query into postgresql . How can I convert this mysql query into postgresql . Here is the mysql query below .

SELECT u.id,c.m_id,u.name,u.email
 FROM messages c, users u
 WHERE (CASE 
 WHEN c.user_one = 1
 THEN c.user_two = u.id
 WHEN c.user_two = 1
 THEN c.user_one= u.id
 END )
 AND (
 c.user_one ='1'
 OR c.user_two ='1'
 )
 Order by c.m_id DESC Limit 20

Spring Data autowiring multiple datasources fails

I have been hacking away at trying to get multiple mysql datasources into a Spring jpa application for what seems like forever. I have yet to find a good, working example online. Below are exceprts from an extremely stripped down app I'm making just to see if it is even possible in a vacuum to be able to autowire and query repositories for more than one MySQL db. No matter what annotations I sub in or out nothing changes, I always get the same error:

Error creating bean with name 'application': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: com.download.integration.repository.primary.PrimaryRepository com.download.Application.PrimaryRepository; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [com.download.integration.repository.primary.PrimaryRepository] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}

Here is my setup. Primary and Secondary beans and repos are in their own packages per the advice of several previously answered items.

first config:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        transactionManagerRef = "primaryTransactionManager",
        basePackages = { "com.download.integration.repositories.primary" })
public class PrimaryConfig{

    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        HibernateJpaVendorAdapter adaptor = new HibernateJpaVendorAdapter();
        adaptor.setShowSql(false);
        adaptor.setGenerateDdl(false);
        adaptor.setDatabase(Database.MYSQL);
        return adaptor;
    }


    @Primary
    @Bean(name = "primaryDataSource")
    public DataSource primaryDataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://127.0.0.1:3306/primarydb");
        ds.setUsername("user");
        ds.setPassword("password");
        return ds;
    }

        @Bean(name = "primaryEntityManager")
        public EntityManager entityManager() {
                return primaryEntityManagerFactory().createEntityManager();
        }

        @Bean(name = "primaryEntityManagerFactory")
        public EntityManagerFactory primaryEntityManagerFactory() {
                LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
                lef.setDataSource(primaryDataSource());
                lef.setJpaVendorAdapter(jpaVendorAdapter());
                lef.setPackagesToScan("com,download.domain.primary");
                lef.setPersistenceUnitName("primaryPersistenceUnit");
                lef.afterPropertiesSet();
                return lef.getObject();
        }

        @Bean(name = "primaryTransactionManager")
        public PlatformTransactionManager primaryTransactionManager() {
                return new JpaTransactionManager(primaryEntityManagerFactory());
        }

}

secondary config:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "secondaryEntityManagerFactory",
        transactionManagerRef = "secondaryTransactionManager",
        basePackages = { "com.download.integration.repositories.secondary" })
public class SecondaryConfig{

    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        HibernateJpaVendorAdapter adaptor = new HibernateJpaVendorAdapter();
        adaptor.setShowSql(false);
        adaptor.setGenerateDdl(false);
        adaptor.setDatabase(Database.MYSQL);
        return adaptor;
    }

    @Bean(name = "secondaryDataSource")
    public DataSource secondaryDataSource() {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUrl("jdbc:mysql://127.0.0.1:3306/secondarydb");
        ds.setUsername("user");
        ds.setPassword("password");
        return ds;
    }

        @Bean(name = "secondaryEntityManager")
        public EntityManager entityManager() {
                return secondaryEntityManagerFactory().createEntityManager();
        }

        @Bean(name = "secondaryEntityManagerFactory")
        public EntityManagerFactory secondaryEntityManagerFactory() {
                LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
                lef.setDataSource(secondaryDataSource());
                lef.setJpaVendorAdapter(jpaVendorAdapter());
                lef.setPackagesToScan("com,download.domain.secondary");
                lef.setPersistenceUnitName("secondaryPersistenceUnit");
                lef.afterPropertiesSet();
                return lef.getObject();
        }

        @Bean(name = "secondaryTransactionManager")
        public PlatformTransactionManager secondaryTransactionManager() {
                return new JpaTransactionManager(secondaryEntityManagerFactory());
        }

}

primary datasource model:

@Entity
public class Primary {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String word;
    private Double weight;
    private Date updated;
    private String sources;

...constructors, getters, setters

secondary datasource model:

@Entity
public class Secondary {

    @Id
    private String uuid;
    private String name;
    private Double weight;

...constructors, getters, setters

And repositories (I have experiments with adding @Repository and seen no change)

public interface Primary extends CrudRepository<Primary, Integer> {
}

public interface Secondary extends CrudRepository<Secondary, Integer> {
}

finally, the main class:

@ComponentScan("com.download")
@EnableAutoConfiguration(exclude = { DataSourceTransactionManagerAutoConfiguration.class, DataSourceAutoConfiguration.class, HibernateJpaAutoConfiguration.class })
@Import({PrimaryConfig.class, SecondaryConfig.class})
public class Application implements CommandLineRunner{

    @Autowired
    PrimaryRepository primaryrepository;
    @Autowired
    SecondaryRepository secondaryrepository;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(String... args) throws Exception {

        search();

    }

    public void search(){
        primaryrepository.findAll();
        secondaryrepository.findAll();
    }

}

Slow MySQL query when using ORDER BY

I have this query:

SELECT article.id
FROM article
INNER JOIN article_cCountry ON article.id = ID1 AND ID2 = 1
INNER JOIN user_cCountry ON article.uId = user_cCountry.ID1 AND user_cCountry.ID2 = 1
LEFT JOIN user ON article.uId = user.ID
WHERE article.released = "TRUE"
AND article.sDate < now()
AND article.cDate != "0000-00-00 00:00:00"
AND (article.eDate > now() OR article.eDate = 0)
AND ( (user.released = true) OR (article.uId = 0) )
ORDER BY article.cDate DESC
LIMIT 0, 10

The query takes ~0.3 seconds, without the ORDER BY only ~0.001 seconds.

Any ideas why the ORDER BY is so slow?

EDIT EXPLAIN:

want to delete oldest record when a new one is added leaving no more then 5 records

I have the following table:

id      user_id     message     timestamp
----    -------     --------    ----------
1        abc        message1    2015-07-27 19:10:40
2        def        message1    2015-07-27 19:20:41
3        xyz        message1    2015-07-27 19:30:41
4        abc        message2    2015-07-28 19:11:40
5        abc        message3    2015-07-28 19:12:40
6        abc        message4    2015-07-28 19:13:40
7        abc        message5    2015-07-28 19:14:40

When user 'abc' adds the 6th message I want the 1st one (oldest) to be deleted.

I'm trying the following two SQL queries but it only leaves 5 messages total not taking into account the user_id

INSERT INTO sample(user_id, message, created)
VALUES('abc', 'message6', now());

DELETE FROM sample 
WHERE user_id = 'abc' 
  AND id NOT IN (SELECT id 
                 FROM 
                     (SELECT id 
                      FROM sample 
                      ORDER BY id DESC 
                      LIMIT 5) x 
                ); 

Thanks in advance

Place MYSQL data into session variable to pass to next page

I'm new to the world of PHP and have been researching things for days but appear to be stuck.

Im attempting to setup a small website to act as a customer account area with a MySQL database at the backend.

I have a webpage with a login form where a user enters their username and password. There is a php script that checks the SQL database to see if there is a match, if there is redirects them to the account page. I found a sample script online which i used to create this and this part works great.

I'm trying to take things a bit further and display data from the matched SQL record on the webpage. From the research ive done it appears the best way to do this is to pass session variables to the account page.

Here is what I have setup

Page 1 - index.php This is a login page with a form that has entry for username and password. On submit, it runs checklogin.php

Page 2 - checklogin.php

<?php
session_start();

$host="localhost:3306"; // Host name 
$username="**********"; // Mysql username 
$password="**********"; // Mysql password 
$db_name="galactek_myecl"; // Database name 
$tbl_name="clients"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// Define $myusername and $mypassword 
$myusername=$_POST['username']; 
$mypassword=$_POST['password']; 

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// set variables
$_SESSION['office_name'] = mysql_query("SELECT office FROM $tbl_name WHERE username='$myusername' and password='$mypassword'");


// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Register $myusername, $mypassword and redirect to   file "login_success.php"
session_register("username");
session_register("password"); 
header("location:myecl.php");
}
else {
header( 'Location: http://ift.tt/1JyyFwT' );
}

?>

Page 3 - myecl.php This is the page where I would like to display that data. Currently im trying to display just the Office Name but it keeps coming up as 0. If I hard-code the office name into the variable on the checklogin.php page, it comes across no problem.

<?php
session_start();
//if(!session_is_registered(myusername)){
// header("location:index.php");
// }
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0    Transitional//EN" "http://ift.tt/kkyg93">
<html xmlns="http://ift.tt/lH0Osb">

<head>
<meta content="en-us" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>

<body>

<p>You're HERE!</p>

<?php

echo $_SESSION['office_name']

?>

</body>

</html>

I'm fairly certain im overlooking something. I haven't had much exposure to PHP to be able to distinguish what the issue is. Any help would be greatly appreciated.

Best practice to keep connection to mysql/mariadb from Python webapp

I init mariadb connection while webapp initialization like this:

con = MySQLdb.connect('localhost', 'user', 'pass', 'db')

Now I've found it doesn't work since there is a timeout to this connection. What is the best practice to set/keep connection to db? Increase timeout, create connection in each request or something more tuned?

Loading a PHP generated .xml file of locations into Google Maps

I am trying to follow the Google Maps tutorials: "Creating a Store Locator with PHP, MySQL & Google Maps" and "Using PHP/MySQL with Google Maps", but have run into something that is beyond my comprehension. I expect the answer is laughably obvious, but here goes...

I have generated an XML file, from the PHP code in the tutorial, that displays the locations I want in my browser. I also have a working Google Map. However, I don't understand how to load the former into the latter.

Inside the downloadUrl function below, I've changed url to xml_output.php - my PHP that extracts the locations I want from my MySQL database. Is this correct? Do I need to change anything else?

function downloadUrl("xml_output.php",callback) {
 var request = window.ActiveXObject ?
     new ActiveXObject('Microsoft.XMLHTTP') :
     new XMLHttpRequest;

 request.onreadystatechange = function() {
   if (request.readyState == 4) {
     request.onreadystatechange = doNothing;
     callback(request, request.status);
   }
 };

 request.open('GET', url, true);
 request.send(null);
}

I also have the following code to produce the markers that contain the XML information. I've added in xml_output.php here too.

downloadUrl("xml_output.php", function(data) {
  var xml = data.responseXML;
  var markers = xml.documentElement.getElementsByTagName("marker");
  for (var i = 0; i < markers.length; i++) {
    var name = markers[i].getAttribute("name");
    var address = markers[i].getAttribute("address");
    var type = markers[i].getAttribute("type");
    var point = new google.maps.LatLng(
        parseFloat(markers[i].getAttribute("lat")),
        parseFloat(markers[i].getAttribute("lng")));
    var html = "<b>" + name + "</b> <br/>" + address;
    var icon = customIcons[type] || {};
    var marker = new google.maps.Marker({
      map: map,
      position: point,
      icon: icon.icon
    });
    bindInfoWindow(marker, map, infoWindow, html);
  }
});

Both tutorials say that the downloadUrl function created takes two parameters (i) url - which I'm assuming refers to my PHP file that creates the XML and (ii) callback. Do I need to replace callback with something? The function name in my PHP file is parseToXML($htmlStr) - does this need to occupy the callback position in the function?

Or is this not working because of something else entirely???

If you can help me, I would be beholden unto you and am prepared to name my first-born child after you.

---

Full code for the whole web page:

<!DOCTYPE html>
<html>
<head>


<script
src="http://ift.tt/1IyejDg">
</script>

<script>

var map;
var myMap=new google.maps.LatLng(51.267867, 0.504360);

function initialize()
{
var mapDiv = document.getElementById('googleMap');
var mapProp = 
    {
    center:myMap,
    zoom:12,
    panControl:true,
    zoomControl:true,
    zoomControlOptions: {style:google.maps.ZoomControlStyle.SMALL},
    mapTypeControl:true,
    mapTypeControlOptions: {style:google.maps.MapTypeControlStyle.DROPDOWN_MENU, position:google.maps.ControlPosition.TOP_Right},
    scaleControl:true,
    streetViewControl:true,
    overviewMapControl:true,
    rotateControl:true,
    mapTypeId: google.maps.MapTypeId.ROAD
    }

map = new google.maps.Map(mapDiv, mapProp);

    var homeControlDiv = document.createElement('div');
    var homeControl = new HomeControl(homeControlDiv, map);
    map.controls[google.maps.ControlPosition.TOP_RIGHT].push(homeControlDiv);

map.setTilt(0);

google.maps.event.addListener(marker,'click',function()
                                    {infowindow.open(map,marker)}
                                );
}

google.maps.event.addDomListener(window, 'load', initialize);

function downloadUrl("xml_output.php",callback) {
 var request = window.ActiveXObject ?
     new ActiveXObject('Microsoft.XMLHTTP') :
     new XMLHttpRequest;

 request.onreadystatechange = function() {
   if (request.readyState == 4) {
     request.onreadystatechange = doNothing;
     callback(request, request.status);
   }
 };

 request.open('GET', url, true);
 request.send(null);
}

downloadUrl("xml_output.php", function(data) {
  var xml = data.responseXML;
  var markers = xml.documentElement.getElementsByTagName("marker");
  for (var i = 0; i < markers.length; i++) {
    var name = markers[i].getAttribute("name");
    var address = markers[i].getAttribute("address");
    var type = markers[i].getAttribute("type");
    var point = new google.maps.LatLng(
        parseFloat(markers[i].getAttribute("lat")),
        parseFloat(markers[i].getAttribute("lng")));
    var html = "<b>" + name + "</b> <br/>" + address;
    var icon = customIcons[type] || {};
    var marker = new google.maps.Marker({
      map: map,
      position: point,
      icon: icon.icon
    });
    bindInfoWindow(marker, map, infoWindow, html);
  }
});


</script>

<title>TEST MAP</title>

<style>
span
{
color:#000000
}
</style>

</head>

<body> 

<hr />

<div style="border:2px solid #0066ff;padding:0px 20px;background:#E6F0FF;width:cover;border-radius:20px;">
<h2>TEST MAP</h2>
<div id="googleMap" style="width:100%;height:380px;"></div>
</div>

<hr />

</body>
</html>

Wordpress video post won't let me enter Youtube video ID

When trying to submit a video, Wordpress requires me to input a valid url like https://www.youtube.com/watch?v=NMbM-ERy2Lk, but if I go to the post afterwards there is an error loading the video because the src of the iframe is

src="https://www.youtube.com/embed/https://www.youtube.com/watch?v=NMbM-ERy2Lk?autoplay=0&wmode=transparent&rel=0&enablejsapi=1&origin=http%3A%2F%2Fcollectively.dev"

So the solution is obviously to just enter in the youtube ID NMbM-ERy2Lk but whenever I try that I get the flash message Please enter a valid URL address and am not allowed to publish or preview the post.

Does anyone know what is going on or how I could disable this message?

Parse, format, and store as variable results from query

In PHP, I get this back:

array(1096) {
  [0]=>
  array(2) {
    ["exuid"]=>
    string(36) "c056b5ce-3b0c-4494-858a-cf184b904dc3"
    [0]=>
    string(36) "c056b5ce-3b0c-4494-858a-cf184b904dc3"
  }
  [1]=>
  array(2) {
    ["exuid"]=>
    string(36) "8a6262c6-a4e0-41a4-8a47-ecaf5f79c2d5"
    [0]=>
    string(36) "8a6262c6-a4e0-41a4-8a47-ecaf5f79c2d5"
  }
  [2]=>
  array(2) {
    ["exuid"]=>
    string(36) "728cb9b6-6240-470f-87d5-706af554cd0b"
    [0]=>
    string(36) "728cb9b6-6240-470f-87d5-706af554cd0b"
  }
  [3]=>
  array(2) {
    ["exuid"]=>
    string(36) "a26d0fdd-9a9b-4611-8c41-3d2c9b012988"
    [0]=>
    string(36) "a26d0fdd-9a9b-4611-8c41-3d2c9b012988"
  }  
    ETC

What I need to end up with is defining a variable that contains all the exuid returned separated by commas. Like this:

$something = 'c056b5ce-3b0c-4494-858a-cf184b904dc3', '8a6262c6-a4e0-41a4-8a47-ecaf5f79c2d5', '728cb9b6-6240-470f-87d5-706af554cd0b', 'a26d0fdd-9a9b-4611-8c41-3d2c9b012988'

I need it in this format to push into another query:

 where `exuid` in ($something)

But I can't parse this correctly. If I do var dump($result) I get the array shown above. If I do var dump($result[0]['exuid']) I get the first value back (c056b5ce-3b0c-4494-858a-cf184b904dc3), but not the others. How do I parse this and format it in the way I need it?

DBSQL Insert value from one entity in a column to another in different column

can u guys tell me if this is correct?? i am trying to insert the value of firstname_1 into first_name.

INSERT INTO wp_usermeta(meta_value) where meta_key=first_name
select meta_value where meta_key=firstname_1

THIS IS THE TABLE (tablename = wp_usermeta) VIEW FROM THE DATABASE

423 16 firstname_1 vrigu
424 16 lastname_2 de
425 16 gender_5 male
426 16 bankname_3 SBI
427 16 accountnumber_4 9456874526
429 17 nickname mithu123
430 17 first_name
431 17 last_name

mysql.jdbc.driver class not found exception

I am building an app which connect to mysql database using intelij idea. I have imported jdbc driver through modules > dependencies and it works great when I run the app through IDE. But when I build an artifact using build > build artifacts > rebuild (tried clean and build also) and I ran it using cmd I get the following error:

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

I have also tried cleaning and rebuilding project but it didnt help.

Any ideas what am I doing wrong?

Updating date in SQL/VB/Asp.Net

I'm pretty much a novice at all this.. I know bits. Just trying to store a date in an SQL database.. I've set it to "06/06/2015" temporarily in code below to see if I can get it to update but it updates it as 01/01/0001. When I suss it, The value I actually want to store is todays date plus 6 months. EG: if its 31/07/2015 today, I want it to store 31/01/2016. Can anyone help ? Much appreciated...

ASPX.VB

Protected Sub imgBtnDatechange_Click(sender As Object, e As ImageClickEventArgs) Handles imgBtn.Click
Dim acc As New accounts(Membership.GetUser().ProviderUserKey)
Dim adjustedDate as Date = "06/06/2015"
acc.UpdateVipEndDate(acc.accountID, acc.adjustedDate)
End Sub

ACCOUNTS.VB

Public Property adjustedDate As Date

Public Sub UpdateVipEndDate(ByVal accountID As Guid, ByVal adjustedDate As Date)
Dim DBConnect As New DBConn
Using db As DbConnection = DBConnect.Conn("DBConnectionString")
    Dim cmd As SqlCommand = DBConnect.Command(db, "UpdateVipEndDate")
    cmd.Parameters.Add(New SqlParameter("accountID", SqlDbType.UniqueIdentifier,       ParameterDirection.Input)).Value = accountID
    cmd.Parameters.Add(New SqlParameter("newadjustedDate", SqlDbType.Date,     ParameterDirection.Input)).Value = adjustedDate
    db.Open()
    cmd.ExecuteNonQuery()
    cmd.Dispose()
    cmd = Nothing
    db.Dispose()
    db.Close()
End Using

End Sub

STORED PROCEDURE

CREATE PROCEDURE [UpdateVipEndDate]
@accountID          uniqueidentifier,
@newadjustedDate    date
AS
BEGIN

UPDATE tblAccounts SET [vipEndDate] = @newadjustedDate WHERE [accountID] = @accountID

END