Cant Figure out a good way to query a datatable in vb.net

So I'm making an application that gets all the events for a month out of a mysql DB and adds them to a calendar. I've got the events in a data table atm "dbTable"

The events are ascending by date

"SELECT * FROM table_events WHERE date BETWEEN '" & startDate & "' AND '" & endDate & "' ORDER BY date ASC"

Now I need to query each day one at a time to check for up to 6 events per date, any suggestions? Im not even sure on how to query the data table let alone do it for up to 31 days and make it somewhat efficient.

MySQL not executing (Java)

For some reason I'm not able to update my database using this code. Can someone help me out? What I've got is when a player completes a certain task it records the time it took them if and only if their time is lower than the best recorded time.

    public void setScores(MapleCharacter chr, int mapid, float time) {
    Connection con1 = DatabaseConnection.getConnection();
    try {
        PreparedStatement ps;
        ps = con1.prepareStatement("SELECT time from jumpquests WHERE characterid = ? AND mapid = ?");
        ps.setInt(1, chr.getId());
        ps.setInt(2, chr.getMapId());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            if (time < rs.getFloat("time")) {
                executeScores(chr, mapid, time);
            }
        } else {
            executeScores(chr, mapid, time);
        }
        rs.close();
        ps.close();
    } catch (Exception Ex) {
        System.out.println("Ran into exception.");
    }
}

public void executeScores(MapleCharacter chr, int mapid, float time) {
    System.out.println("Setting scores for " + chr.getName() + " for map " + mapid + " at time " + time);
    Connection con1 = DatabaseConnection.getConnection();
    try {
        PreparedStatement ps = con1.prepareStatement("REPLACE INTO jumpquests (characterid, mapid, time) values (?,?,?)");
        ps.setInt(1, chr.getId());
        ps.setInt(2, chr.getMapId());
        ps.setFloat(3, time);
        ps.close();
    } catch (Exception e) {
        System.out.println("Executing scores ran into exception.");
    }
}

duplicate data using INNER JOIN issue

I am building a project and want to retrieve data from three different tables so am using INNER JOIN but just worked out it is duplicating the data, below is what it is currently doing

Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Insurance expiry date: 30 July 2015
Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Tax expiry date: 30 June 2015

what I want is the following

Name: Ian Haney
First Line of Address: 12C Barclays Bank Chambers
Second Line of Address: Broadway North
Town: Pitsea
County: Essex
Postcode: SS13 3AU
Telephone Number: 01268 206297
Mobile Number: 07538 503276
Car Model: Jeep
Car Number Plate: AB10 1AB
Tax expiry date: 30 June 2015
Insurance expiry date: 30 July 2015
MOT expiry date: 30 August 2015

is that possible to do?

my coding is below

<?php

if (logged_in() == false) {
    redirect_to("login.php");
} else {
    if (isset($_GET['id']) && $_GET['id'] != "") {
        $id = $_GET['id'];
    } else {
        $id = $_SESSION['user_id'];
    }

    ## connect mysql server
        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        # check connection
        if ($mysqli->connect_errno) {
            echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
            exit();
        }

    ## connect mysql server
        $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        # check connection
        if ($mysqli->connect_errno) {
            echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
            exit();
        }
    ## query database
        # fetch data from mysql database

        $sql = "SELECT v.visitor_id, visitor_name, visitor_email, visitor_firstline, visitor_secondline, visitor_town, visitor_county, visitor_postcode, visitor_tel, visitor_mobile, visitor_model, visitor_plate, item.description, renewal_id, DATE_FORMAT(renewal_date, '%e %M %Y') as datedue, renewal_date FROM visitors v 
        INNER JOIN renewal USING (visitor_id)
        INNER JOIN item USING (item_id)
        WHERE renewal_date >NOW()";


        if ($result = $mysqli->query($sql)) {
            $user = $result->fetch_array();
            } else {
            echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>";
            exit();
            }

    if(mysqli_num_rows($result)) {  

   //fetch the data from the database 
while ($row = mysqli_fetch_array($result)) {

# echo the user profile data
            /*echo "<p>User ID: {$user['id']}</p>";*/
            echo "<p>Name: {$user['visitor_name']}</p>";
            echo "<p>First Line of Address: {$user['visitor_firstline']}</p>";
            echo "<p>Second Line of Address: {$user['visitor_secondline']}</p>";
            echo "<p>Town: {$user['visitor_town']}</p>";
            echo "<p>County: {$user['visitor_county']}</p>";
            echo "<p>Postcode: {$user['visitor_postcode']}</p>";
            echo "<p>Telephone Number: {$user['visitor_tel']}</p>";
            echo "<p>Mobile Number: {$user['visitor_mobile']}</p>";
            echo "<p>Car Model: {$user['visitor_model']}</p>";
            echo "<p>Car Number Plate: {$user['visitor_plate']}</p>";       
            echo "<p>" . $row['description'] . " expiry date: " . $row['datedue'] . "</p>\n";

}
        } else { // 0 = invalid user id
            echo "<p><b>Error:</b> Invalid user ID.</p>";
        }

}

?>

Select Last Distinct Value and TIMEDIFF Based on Selection?

I'm trying to setup a query that does a DATEDIFF between two times, based on when the last unique value in one column is present. The data is structured as follows:

  row   ticket_id   create_time      change_time    owner_id     queue_id
 1         11234    5/12/2014 13:47 5/12/2014 13:47        2        4
 2         11234    5/12/2014 13:47 5/12/2014 13:47        2        4
 3         11234    5/12/2014 13:47 5/12/2014 13:47        8        11
 4         11234    5/12/2014 13:47 5/12/2014 13:47        8        11
 5         11234    5/12/2014 14:02 5/12/2014 14:02        3        9
 6         11234    5/12/2014 14:10 5/12/2014 14:10       17        5
 7         11234    5/14/2014 12:00 5/14/2014 12:00       17        5
 8         11234    5/15/2014 12:27 5/15/2014 12:27       17        5

Basically, I want to do a datediff between rows 6 and 8 for the "change_time" column. I want to select the final distinct number in either the owner_id column or queue_id column for each ticket_id and calculate the difference in change times. Is there a way this could be setup using MySQL? Using a MAX() function won't work unfortunately because highest and second highest change times are not always associated with the final queue id or owner id. I know in SAS a similar operation can be performed using a combination of do loops and counter+1, but is something like this possible with SQL?

Encrypt Amazon RDS

I want to create a RDS with MySQL on it, and I want it to be encrypted.

I am using the Ruby API, and I've looked into the RDS client API, and I saw that there are params that can be given:

tde_credential_arn
tde_credential_password

but both are related to oracle DB (Encrypting Amazon RDS Resources). I've also tried to use key storage_encryped and give it a true value, but the key wasn't a valid one (also I've seen it here: CreateDBInstance).

So, how can i do it with MySQL RDS ?

Where to find GoDaddy hostname for MySQL server?

the GoDaddy website updated a while back and now you can't view your hostname though the "details" button because the button no longer exists. I'm trying to set up a MySQL server on GoDaddy but I can't find the hostname of the server which I need to use mysql_connect().

Does anyone know where to find this information now? Thanks.

how to store json in database

Is this better

{"details":{"number":"8","date":"29/06/2015","due_date":"06/07/2015"},

or this one:

%7B%22details%22%3A%7B%22number%22%3A%228%22%2C%22date%22%3A%2229%2F06%2F2015%22%2C%22due_date%22%3A%2206%2F07%2F2015%22

to store in a database?

Thank you.

error when writing variable to mysql via python

I am trying to insert with python (v 2.7.6) a variable with multiple entries into mysql (Ver 14.14 Distrib 5.5.43) table. The code is as follows :

cur = con.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS Stations(     \
            StationsID  INT AUTO_INCREMENT,           \
            Code      VARCHAR(3)       ,              \
            PRIMARY KEY pk_Stations (StationsID)      \
            );");

cur.executemany("INSERT INTO Stations (Code) VALUES(?)", sns);

sns variable has the following form:array(['PAL', 'TT1', 'BAL', 'MHD', 'BI5', 'CB4'],dtype='|S3')

I am getting the following error: File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 199, in executemany if not args: return ValueError: The truth value of an array with more than one element is ambiguous. Use a.any() or a.all()

Could you please give me a help here?

can`t install flask-mysql fedora

i`m trying to install flask-mysql with pip on fedora 22, but I get this error:

Collecting flask-mysql
  Using cached Flask_MySQL-1.3-py2.py3-none-any.whl
Requirement already satisfied (use --upgrade to upgrade): Flask in /usr/lib/python2.7/site-packages (from flask-mysql)
Collecting MySQL-python (from flask-mysql)
  Using cached MySQL-python-1.2.5.zip
    Complete output from command python setup.py egg_info:
    sh: mysql_config: command not found
    Traceback (most recent call last):
      File "<string>", line 20, in <module>
      File "/tmp/pip-build-IH7lNv/MySQL-python/setup.py", line 17, in <module>
        metadata, options = get_config()
      File "setup_posix.py", line 43, in get_config
        libs = mysql_config("libs_r")
      File "setup_posix.py", line 25, in mysql_config
        raise EnvironmentError("%s not found" % (mysql_config.path,))
    EnvironmentError: mysql_config not found

    ----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-IH7lNv/MySQL-python

I searched a lot, but all the solutions were for debian based os`s which needed to download libmysqlclient-dev but there is no such package in fedora

Password reset PHP

I am new to website design and I have recently made a website and i would like to add a reset password function, it doesn't work.

The SQL connection is inside of the init.php file

<?php
include('core/init.php');
include('includes/overall/header.php');
echo "
<h1>Reset Password</h1>


<div class='Reset' align='center'>
<form action='forgot_pass.php' method'POST'>
Enter your username<br><input type='text' name='username'><p>
<br>
Enter your email<br><input type='email' name='email'><p>
<input type='submit' value='Submit' name='submit'>
</form>
</div>
";

if (isset($_POST['submit']))
{
$username = $_POST['username'];
$email = $_POST['email'];

$query = mysql_query("SELECT * FROM `users` WHERE `username`='$username'");
$numrow = mysql_num_rows($query);

if ($numrow!=0)
{
    while($row = mysql_fetch_assoc($query))
    {
        $db_email = $row['email'];
    }
    if ($email == $db_email)
    {
        $code = rand(10000,1000000);

        $to = $db_email;
        $subject = "Password Reset";
        $body = "

        Automated email. Click the link
        http://ift.tt/1U1Buug

        ";

        mysql_query("UPDATE users SET passreset='$code' WHERE username='$username'");
        mail($to,$subject,$body);

        echo "Check Email";
    }
    else
    {
        echo "Email not correct";
    }
} else {
    echo "That user does not exist";
    }


}

?>

I will be so happy if somebody could help me thanks

How to become MySQL trainer?

I'm a certified MySQL developer and I have over a decade of SQL experience, most of it MySQL.

What are the steps to take to became a MySQL trainer?

Paypal delay inserting into database

I created a login/register system. After registration users must select payment option clicking on paypal buttons. After payment has been done, users return to my website. Behind all this process, I insert paypal variables into database along with username from my $username = $_SESSION['username'].

Problem is that paypal variables have a considerable delay to be inserted into my database. Then, only username is being inserted. payer_email, item_name, item_number are empty. If I remove username isertion, everything is inserted after a while. Any solution for this?

PHP Optmization: Processing millions of MySQL records?

I've got a handful of databases with, potentially, millions of records that I need to run some backend services on pretty frequently (backups, reporting, etc)

Currently I'm batching my requests in batches of 1k to try and speed things up. It helps a little, but not really enough to notice. Some reports customers want to generate can take a few days - which is preposterous.

Then there's backups. My company has a unique way to store our records and we need to process each and every one individually so I can't just export everything into a file and save it off site.

So, as you can imagine, this causes a lot of backlog pretty quickly.

What are some methods I can look into for speeding this up?

I've already examined using mysqli_poll to help, but I still end up with blocking methods while each batch gets processed.

Is threading with pthreads really my only option to dramatically speed things up at this point or do I need to convince the superiors to use something that's actually threaded to make this actually work.

Thanks in advance for your help!

"IDENTIFIED BY 'password'" in MySQL

I often see in many MySQL tutorials that people use command IDENTIFIED BY 'password' both during user creation and granting him privileges.

For example:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED BY 'password';

I tried using GRANT without IDENTIFIED BY and it works.
Can somebody explain me why it is used twice? Could there be other password for specific privileges?

MySQL group by all columns except one

I'm looking for a (cleaner?) way to do the following:

Let's say I have a table, main, with ~15 columns that looks something like this, with one row per id:

main:
id      start           end             col4    ...     col15
666     2014-01-01      2014-06-30      ...     ...     ...
1234    2015-03-05      2015-05-02      ...     ...     ...
9876    2014-09-01      2015-01-01      ...     ...     ...
...(etc)

Then I have another table, events, which may have 0, 1, or many rows per id:

events:
id      date            code
666     2014-01-20      "code_a"
1234    2015-05-01      "code_b"
666     2014-01-25      "code_c"
666     2014-02-09      "code_z"
... (etc)

and finally I have a table, codes, which has one row per code, giving a description for the code as well as a type (0,1, or 2):

codes:
code            desc            type
"code_a"        "something"     0 
"code_b"        "somethn else"  1
"code_c"        "another thing" 0
"code_d"        "one more"      2
(no code z)

and what I want as a result is main's 15 columns plus three additional columns which contain comma separated lists of event codes which happened between the start and end dates for that id by type (first column is type 0, second type 1, third type 2), so:

id      start           end             ...     col15   type_0          type_1  type_2
666     2014-01-01      2014-06-30      ...     ...     "code_a,code_c"         
1234    2015-03-05      2015-05-02      ...     ...                     "code_b"
...(etc)

my solution is

select m.*
     , group_concat(c0) as type_0
     , group_concat(c1) as type_1
     , group_concat(c2) as type_2
from main m 
     left join events e on m.id = e.id and e.date between m.start and m.end
     left join codes c0 on c0.code = e.code and c0.type = 0
     left join codes c1 on c0.code = e.code and c0.type = 1
     left join codes c2 on c0.code = e.code and c0.type = 2
group by m.id
       , m.start
       , m.end
       , m.col4
       , m.col5
       , m.col6
       , m.col7
       , m.col8
       , m.col9
       , m.col10
       , m.col11
       , m.col12
       , m.col13
       , m.col14
       , m.col15  

But to me that's pretty nasty looking. Is there a more elegant way to do this (especially avoiding the 15 columns listed in the group by)?

(PHP, mysql) Copy column values to another column in the same table

I'm trying to copy title column to keywords column in database, so the keywords will be inserted automatically from the title.

http://ift.tt/1C2Pa2I

I want to add comma ', ' before each word for example.

" It's my first program "   

it will turn into

" It's, my, first, program, "

This the code I wrote.

<?php

  // $id =mysql_insert_id;
  $select_posts = mysql_query("SELECT * FROM `posts`");

  while($row = mysql_fetch_array($select_posts)){
        $id  = $row['post_id'];
        $text =  $row['post_title'];  

       $delim = ' \n\t,.!?:;';
       $tok = strtok($text, $delim);


    while ( $tok !== false){
          echo $tok1 = $tok.',';
          mysql_query("UPDATE `posts` SET  `post_keywords` =  '$tok1' WHERE `post_id` = $id  ");
          $tok = strtok($delim);
        }   
}

?>    

it insert the last word in each title column , because the words is overwritten by while loop.

Please help me .

MS SQL Read value with apostrophe from table and save it in another table

I am reading a value from table with apostrophe with which I create a dynamic query and than I run a sp to save it in another table, which works fine without apostrophe but throw an error when it contains an apostrophe.

e.g. set @sql = 'exec nameOfSP' + @arguments

@arguments value comes from database

Clear MySQL Cache

I took over a project written in Laravel 4. We have MySQL 5.6.21 - PHP 5.4.30 - currently running on Windows 8.1.

Every morning on the first attempt to access the landingpage - which contain about 5 queries on the backend - this site will crash with a php-timeout (over 30 seconds for response).

After using following I got closer to the cause: Laravel 4 - logging SQL queries. One of the queries takes more than 25 seconds on the first call. After that its always < 0.5 seconds.

The query has got 3 joins and 2 subselects wrapped in Cache::remember. I want to go into optimizing this so that on production it won't run into this problem.

So I want to test different SQLs The Problem is that the first time the data gets cached somehow and then I can't see whether my new SQL's are better or not.

Now, since I guess it's a caching issue (on the first attempt it takes long, afterwards not) I did these:

MySQL: FLUSH TABLES;
restart MySQL
restart Apache
php artisan cache:clear

But still, the query works fast. Then after some time I don't access the database at all (can't give an exact time, maybe 4 hours of inactivity) it happens again.

Explain says:

1 | Primary | table1 | ALL | 2 possible keys | NULL | ... | 1010000 | using where; using temporary; using filesort
1 | Primary | table2 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table3 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table4 | eq_ref | PRIMARY | PRIMARY | ... | 1 | NULL
3 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where
2 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where

So here the questions:

• What's the reason for this long time?
• How can I reproduce it? and
• Is there a way to fix it?

I read mysql slow on first query, then fast for related queries. However that doesn't answer my question on how to reproduce this behaviour.

---

Update

I changed the SQL and now it is written like:

select 
    count(ec.id) as asdasda

from table1 ec force index for join (PRIMARY)
    left join table2 e force index for join (PRIMARY) on ec.id = e.id
    left join table3 v force index for join (PRIMARY) on e.id = v.id 

where
    v.col1 = 'aaa'
    and v.col2 = 'bbb'
    and v.col3 = 'ccc'
    and e.datecol > curdate()
    and e.col1 != 0

Now explain says:

+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
| id | select_type | table  | type   | possible_keys | key          | key_len | ref             | rows   | Extra       |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | table3 | ALL    | PRIMARY       | NULL         | NULL    | NULL            | 114032 | Using where |
|  1 | SIMPLE      | table2 | ref    | PRIMARY       | PRIMARY      | 5       | table3.id       |     11 | Using where |
|  1 | SIMPLE      | table1 | eq_ref | PRIMARY       | PRIMARY      | 4       | table2.id       |      1 | Using index |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+

Is that as good as it can get?

SELECT MIN and MAX of column1 by another distinct column2 and fetch entire row

Ok, so I have the table Television that has over 1,000 records and looks like this:

ID     Code    Source   Brand       Price
-----------------------------------------
930    A584    C11      Panasonic   512
843    VG873   U19      Sony        590
301    A584    J63      Panasonic   494
738    D900    T32      Samsung     378
786    VG873   Y91      Sony        575
409    E764    G48      LG          435
912    VG873   Y91      Sony        535
626    E764    H14      LG          460
581    E764    C55      LG          455
557    D900    I42      Samsung     390

I'm trying to run a query that would fetch the lowest price from each distinct brand where price is greater than or equal to $400, fetching the entire row. The result on the above example set should look like this:

ID     Code    Source   Brand       Price
-----------------------------------------
301    A584    J63      Panasonic   494
409    E764    G48      LG          435
912    VG873   Y91      Sony        535

I tried some answers of somewhat similar questions in here but the results were off by a mile. The last I tried is the following and it was the closest but still not giving the desired result:

SELECT tv.* FROM Television tv
    INNER JOIN (SELECT Brand, MIN(Price) AS MinPrice 
    FROM Television
    GROUP BY Brand) groupedTV
ON tv.Brand= groupedTV.Brand
AND tv.Price= groupedTV.MinPrice
WHERE tv.Price>=400

Any help is appreciated.

Edit: Corrected the result set (ID 301 should be the lowest in Panasonic). Thanks to @wilfo.

Given date or greater than system date validation in mysql (sample query given)

i need to add a where condition something like this below

where future_date='12/31/9999 12:59:59' or future_date > current system date.

How do i do this in my sql?

optimizing a Slow MySQL query using EXPLAIN output

The following query took 6.6 seconds to run, and yielded 26 rows.

EXPLAIN result is two SIMPLE queries of type 'ref', using keys, scanning 23 and 48 rows.

Table f has 1000 rows and table m has 42000 rows.

seltype table type keys                 key                 keylen  ref             rows filtered extra

SIMPLE  f     ref  PRIMARY,             forum_site_id       4       const           23   100.00   Using where; Using temporary; Using filesort
                   forum_site_id,
                   forums_flag_list_new_posts

SIMPLE  m     ref  forum_msg_forum_id,  forum_msg_forum_id  5       locali_db.f.id  48   100.00   Using where
                   forum_msg_status,
                   forum_msg_date   

Here is the query (quite a simple one):

SELECT

    m.id AS msg_id,
    m.public_id AS msg_public_id,
       more fileds of this table ...

    f.id AS forum_id,
    f.public_id AS forum_public_id,
       more fileds of this table ...

FROM

    forum_msgs m
    INNER JOIN forums f ON
        m.forum_id = f.id

WHERE

    f.site_id = 19
    AND f.flag_list_new_posts = 1

    AND m.msg_date >= 1434803744
    AND m.status <> 11

ORDER BY
    m.msg_date DESC

LIMIT 
    100

All fields in WHERE and ORDER BY clauses are of type INTEGER and are defined as INDEX. The field forum_id is defined as FOREIGN KEY.

I would be happy to find out what may be causing the outrageous performance :)

Does order matter in MySQL for short circuiting of predicates?

Let's say I have to run the SQL query:

SELECT data FROM table WHERE condition1 AND condition2 AND condition3 AND condition4 

Is that any different than

SELECT data FROM table WHERE condition3 AND condition1 AND condition4 AND condition2

?

---

If it's not different:

I know from my own experience that condition1 is less expensive than condition2 is less expensive than condition3 is less expensive than condition4.

If any of the prior conditions are not met, the remaining conditions should never be checked. It wouldn't be immediately obvious to the optimizer, as stored functions are involved. How should I write a query that does this?

SQL - select rows not similar to above in the same column in one table

I've got the following result when I tried to fetch the results from the database with mysql command :

select player_id,full_name,club_name from players

something like that :

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   Petrojet  |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

but I have many club names similar to above

+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+

OR

+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+

and I have tried to use ORDER BY RAND(club_name) and it gave me a result like that

Ex:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   Petrojet  |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

The desired output would be:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   Petrojet  |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Cocorico  |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

can mysql do that or should I integrate php with mysql ? Any help would be much appreciated.

how to filter all columns together having not null value in sql server

I have about 20 columns and want to filter all of them together having only 'not null' values. Is there a way to do this in sql since I don't want to mention all column names in my query.

Something like this -

Select * from table_name where columns IS NOT NULL

In MySQL table selecting only when a field is not empty or not ' '

my majority of values are filled but set_price column has few empty field. i want to select only when set_price is not empty or not ''. i am using mysql and datatype set for set price is set_price varchar(50) NOT NULL,

MY Function:

 function getAvilableServiceDetails($service_id,$select_from,$select_to,$select_weight,$dimesion_height,$dimesion_width,$dimesion_girth,$price_shorting)
            {
                global $objSmarty;  

        //  echo $service_id."//".$select_from."//".$select_to."//".$select_weight."//".$dimesion_height."//".$dimesion_width."//".$dimesion_girth."//".$price_shorting; exit;

                if($select_weight=='')
                    $select_weight=0;

                if($dimesion_height!='')
                $height_condition=" and  dimesion_height>=".$dimesion_height;
                else
                $height_condition="";

                if($dimesion_width!='')
                $width_condition="  and  dimesion_width>=".$dimesion_width;
                else
                $width_condition="";

                if($dimesion_girth!='')
                $girth_condition=" and dimesion_girth>=".$dimesion_girth;
                else
                $girth_condition="";



                //===================searching condition=========================//
                    if($_SESSION['package_search_by']!='')
                        $searching_condition=" and service_type='".$_SESSION['package_search_by']."'";
                    else
                        $searching_condition="";    

                //=====================price shorting============================//
                if($price_shorting!='')
                {
                    if($price_shorting=='L_TO_H')
                        $order_by=" group by unique_id order by set_price ASC ";
                    else    
                        $order_by=" group by unique_id order by set_price DESC ";
                }
                else
                {
                    $order_by=" group by unique_id order by set_price ASC ";
                }

                if($select_from!='' && $select_to!='' && $service_id!='')
                {
                   $select_query="select * from set_delivery_package_details  where service_id='".$service_id."' and 
                    dimesion_weight>=".$select_weight." and set_price IS NOT NULL and set_price<>'' and FIND_IN_SET ('".$select_from."',sel_countrys) and FIND_IN_SET ('".$select_to."',sel_countrys)  and deleted='N' and status='Y' ".$searching_condition.$height_condition.$width_condition.$girth_condition.$order_by;
                    $this->dbh->Query($select_query);
                    if($this->dbh->num_rows>0)
                    {
                        $PackageData=$this->dbh->FetchAllResults($select_query);
                        $objSmarty->assign("PackageData", $PackageData);
                    }
                    else
                    {
                        $searching_error_message="Record Not Found";
                        $objSmarty->assign("searching_error_message", $searching_error_message);
                    }


                }
                else
                {
                    $searching_error_message="Record Not Found Please Search Again";
                    $objSmarty->assign("searching_error_message", $searching_error_message);
                }
            }

The table which i am using: please click here

mysqli display data based on row ID value

I need to display an text in an row, based on an value from other table, I will explain... step by step to be all very CLEAR.

The table called "links" have an row called "title_id"

This row "title_id" from "links", have THE SAME value in other table called "titles" in the row named "id".

The table "titles" have another row called "title" - and tis is wath i need to display in this way...

a href= ..../>TABLE links row title_id = to TABLE titles row id -> DISPLAY HERE table titles row title (based on row title_id from the table links) < /a...

I hope you can understand, the result must be (a href=...>TEXT MOVIE TITLE< /a>)

Here is the base code, wath I need to change (I have commented the part wath need changes):

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "dbname";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "SELECT id, label, title_id, season, episode, approved FROM links WHERE approved = 1 order by id desc LIMIT 30 OFFSET 1";
    $result = $last_id = $conn->query($sql);


    if ($result->num_rows > 0)
     {
        echo "<table><tr><th>ID</th><th>Audio</th><th>URL</th><th>Temporada</th><th>Episodio</th><th>Aprobado</th></tr>";
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo "<tr><td>".$row["id"]."</td><td>".$row["label"]."</td>";
            echo "<td>";
            if (empty($row['episode'])) {
         echo "<a href=";
         echo '/peliculas-online/'.$row["title_id"];
         echo ">HERE MUST HAVE THE VALUE FROM TABLE titles -> row title - based on $row["title_id"] </a>";
// -------------------------
        }
        else {
        echo "<a href=";
         echo '/series-online/'.$row['title_id'].'/seasons/'.$row['season'].'/episodes/'.$row["episode"];
         echo ">HERE MUST HAVE THE VALUE FROM TABLE titles -> row title - based on $row["title_id"] </a>";
// -----------------------------------
            }
            echo "</td>";
            echo "<td>".$row["season"]."</td><td>".$row["episode"]."</td><td>".$row["approved"]."</td></tr>";


        }
        echo "</table>";
    } else {
        echo "0 results";
    }
    $conn->close();
    ?>

Wordpress custom database query

I am trying to get the result from the database with the mysql LIKE but in wordpress its not working here is the code of what i am trying

//this is what i am putting in where clause.
$state = $_POST['state'];
//table name.
$table_name = $wpdb->prefix . 'userprofile';
//trying but this is returning empty
$q = 'SELECT * FROM ' . $table_name . 'WHERE state LIKE \'%' . esc_sql( like_escape( $state ) ) . '%\'';
echo $q;
$result = $wpdb->get_results($q);
if (empty($result)) {
    echo "the result is empty";
}
//returns empty array.
print_r($result);

SQL Sort by name alphabetically

I have a problem on sorting by alphabet but I want the name with [duplicate] sort at last What I would like to have is at first would sort by alphabet then name with duplicate sort at last. The duplicate might be more than 1.

The result I have done:

$sql  = 'SELECT DISTINCT venue_id, name FROM venues ';
$sql .= 'ORDER BY CASE ';
$sql .= 'WHEN name NOT LIKE "%[DUPLICATE]" THEN "1" ';
$sql .= 'WHEN name LIKE "%[DUPLICATE]" THEN "100" ';
$sql .= 'END ASC ';


   *----------*------------------*
    |     ID   |  venue_name     |
    *----------*------------------*
    | 1       | Axxxx            |
    | 2       | Assss            |
    | 5       | Assss [duplicate]| // this is actually the whole name of the venue
    | 6       | Bzzzz            |
    | 7       | Bzzzz [duplicate]|
    | 8       | Fzzzz            |
    | 21      | Fzzzz [duplicate]|
    *----------*-----------------*

is it posible for the result be like:

*----------*------------------*
|     ID   |  venue_name     |
*----------*------------------*
| 1       | Axxxx            |
| 2       | Assss            |
| 6       | Bzzzz            |
| 8       | Fzzzz            |
| 5       | Assss [duplicate]|
| 7       | Bzzzz [duplicate]|
| 21      | Fzzzz [duplicate]|
*----------*-----------------*

Python is printing one list backwards but not the others

I'm using Django 1.8 with a MySQL backend. I need to get two columns from several tables in a database and concatenate them, then put the sorted results on a webpage. I have this so far:

views.py

def herps(request):
    #performs queries to get distinct species from the database

    trQuery = queryBuilder(HetrRec, '_hetr')
    opQuery = queryBuilder(HeopRec, '_heop')

    valList = opQuery + trQuery

    query = sorted(set(valList))
return render_to_response('herps.html', {'queries': query,})

The function to perform the query on the database looks like this:

def queryBuilder(table, column_str):

    genus = 'genus' + column_str
    species = 'species' + column_str
    filterSp = {species: 'sp.'}

query = table.objects.order_by(species, genus).values(genus, species).distinct().exclude(**filterSp)
valueList = []
for q in query:
    species = ' '.join(q.values())
    valueList.append(species)

return(valueList)

So the output comes out in several table combinations as Genus Species. In one table it comes out Species Genus.

I've been through thte data to remove any characters that might affect it (leading / trailing spaces, incomplete pairs etc) but no joy.

Lotus Notes NSF conversion

I seen a few things but I haven't found anything great regarding the conversion of

Lotus Notes (NSF) files to MySQL.

Any thoughts?

post deployment script to run a script only one time

If I want to have the post deployment script run a script only one time is there a way to do this?

I want to run post deployment scripts only once for my database

Calculate php mysql results sum dynamically

I have a table with product price model cost stock etc, to make it easier I calculate the total customer pay for for each product like this

<?php echo number_format($show['quantity'] * $show['product_price'],0,',','.'); ?>

I need to show the total sum of this calculation but as you can see they are calculared in PHP real time. Is there a way to do it?

Here is the complete code

<?php
$result=mysqli_query($database,"SELECT * FROM `products` order by `category` ASC");
$rows=mysqli_num_rows($result);
if(mysqli_num_rows($result)>0){
?>

<table class="sales">

<tr>
<td>Quantity</td>
<td>Product Cost</td>
<td>Customer Pays</td>
</tr>        

<?php if($rows){$i=0;while($show=mysqli_fetch_assoc($result)){?>

<tr>
<td><?php echo number_format($show['quantity'],0,',','.'); ?></td>
<td><?php echo number_format($show['product_cost'],0,',','.'); ?></td>
<td><?php echo number_format($show['quantity'] * $show['product_cost'],0,',','.'); ?></td>
</tr>

<?php }}?>
</table>

TOTAL CUSTOMER PAY FOR ALL PRODUCTS = EXAMPLE $10.234

If there are 5 different products in the results with different prices and different Customer Pays I need to sum all those Customer Pays and show here

<?php }else{?> 

No products to show

<?php }?>

ERROR 1045 (28000): Access denied for user 'ashwini'@'localhost' (using password: NO)

I've installed the LAMP stack on my Ubuntu 14.04 . when I try to run mysql, it shows the error :

ERROR 1045 (28000): Access denied for user 'ashwini'@'localhost' (using password: NO)

I've tried reinstalling the LAMP stac, MySQL, and tried all the possible things. But at last, i get the same error. Please tell me how to come over this?

How to handle java date with mysql datetime column

I have a system that consumes a third-party WebService. This WebService gives me a lot of fields about a ticket. Three of these fields are dates with Unix Timestamp format (below I'll give examples with values..). I save these fields in MySQL datetime columns using MyBatis.

My problem is when I display the time to the user, for example:

I receive this value from the webservice: 1435618800 , using this site to convert to date I get this value: Mon, 29 Jun 2015 23:00:00 GMT.

Inside my application, I get the value in minutes (1435618800) from the web service and I convert to Date using :

Date date = Date.from( Instant.ofEpochSecond( Long.parseLong(StringValueFromWS)));

I set this date to a ticket object, then I pass this ticket object to MyBatis to save to my table. When I look inside the table, I see the value 2015-06-29 20:00:00. When I show this value, the users see the following time : 2015-06-29 17:00:00, they should be seeing 2015-06-29 20:00:00.

Here is the create table:

CREATE TABLE `ticket` (
  `id_ticket` int(11) NOT NULL AUTO_INCREMENT,
  `open_date` datetime DEFAULT NULL,
  `callback_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id_ticket`)
);

Here is the xml from my insert and select from mybatis:

<select id="getTicket" parameterType="PaginationFilter" resultType="Ticket">
    <![CDATA[
        SELECT
            t.id_ticket as 'id',
            t.open_date as 'openDate',
            t.callback_date as 'callbackDate'

        from ticket as t
        WHERE t.id_ticket = XXX 
    ]]>
</select>

<insert id="insertTicket" parameterType="Ticket" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO ticket 
    (
        `open_date`,
        `callback_date`
    ) 
    VALUES 
    (
        #{openDate}, 
        #{callbackDate}
    );
</insert>

My doubt is what`s the most elegant solution for this. Should I add/discount the GMT offset to the date field before inserting into MySQL to have the GMT 00:00 inside my database or is there any better solution?

I solved this problem adding 3 hours to my dates, but I would like to know a better solution 'cause I didn't like this one.

Some additional information:

• Java version 1.8.0_45
• Mybatis 3.0.5
• MySQL 5.5.15
• I'm at GMT -03:00 (Brazil - Brasilia)

best way to store a 3k string on mysql varchar vs text for infrequent reads

I decided to store a json string on mysql database. The data will be read infrequently, maybe 5-10 times max, where 10 times is highly unlikely. So what are my most logical options? TEXT BLOOB VARCHAR?

Will TEXT OR BLOOB be more suitable for this situation or I should stick with VARCHAR? I know varchar is stored inline and text/bloob stored offtable.

Any help would be appreciated!

SQL: Group time By every 15 minutes

I have a table with the following structure

id      date      timestamp  licenseid   storeid    deviceid    value
1     2015-06-12   17:36:15   lic0001       1         0add      52
2     2015-06-12   17:36:15   lic0002       1         0add      54
3     2015-06-12   17:36:15   lic0003       1         0add      53
4     2015-06-12   17:36:21   lic0001       1         0add      54
5     2015-06-12   17:36:21   lic0002       1         0add      59
6     2015-06-12   17:36:21   lic0003       1         0add      62
7     2015-06-12   17:36:21   lic0004       1         0add      55
8     2015-06-12   17:36:15   lic0001       1         0bdd      53
9     2015-06-12   17:36:15   lic0002       1         0bdd      52
10    2015-06-12   17:36:15   lic0003       1         0bdd      52

I need to check if any of the license has stopped reporting values. The approach I am thinking of is getting time_stamps in group of 15 minutes and then checking if the data is missing for any license for longer duration and then deep diving.

Need help with Query that can group by time_stamp every 15 minutes starting from 00:00 and showing count of values within that time duration. I have tried the below query but the time stamp doesn't show the correct value. It is showing just one row with timekey as 0.

SELECT count(*),  round(unix_timestamp(time_stamp)/(15*60)) AS timekey
FROM     data
GROUP BY timekey;

PS: I have used this query from this post as stated above but the query returns 0 for timekey : Group mysql query by 15 min intervals

Problems with PHP date format

I decided to make this questions because something very rare is happening with my project!

I will explain:

I have this html code:

<input type="text" name="dob" value="<?php echo $rows['dob']; ?>"></input>

the value of this field is for example: 12-24-1962 because I convert the date that exist in the SQL database with this code:

$sql = "select DATE_FORMAT(dob, '%m-%d-%Y') AS dob,   .....

So later maybe the user edit the DOB (date of birth) field and I update this value in the database with this php code:

$dob_0 = DateTime::createFromFormat('m-d-Y', $_POST['dob']);
$dob   = $dob_0->format('Y-m-d');

$sql = "UPDATE $tbl_name SET dob = '$dob', .......

So with this the date arrive to database with this format: YYYY-MM-DD

My doubt is:

Can pass weeks and nothing happen but sometimes (random) I dont know why this exact code produce a fatal error like this:

Fatal Error: Call to a member function format() on a non-object in...

And the line of the error is this:

$dob_0 = DateTime::createFromFormat('m-d-Y', $_POST['dob']);

What is happening? Why does the code work for some time and break later?

How to Connect R to MySQL Database Using ODBC on Mac OS 10.10.3 (Yosemite)

Disclaimer upfront: I'm not a programmer, so...be gentle.

I've spent the past couple of days trying to get R studio on my Mac to connect to a MySQL database. The first hurdle faced was when running the command to install the ODBC package:

configure: error: "ODBC headers sql.h and sqlext.h not found" ERROR: configuration failed for package 'RODBC' ** Removing '/usr/lib64/R/library/RODBC'

After a lot of googling, I figured out this was solved by installing and updating something called brew, by running this command:

brew update && brew install unixODBC && \ wget "http://ift.tt/1KpD9XN" && \ R CMD INSTALL RODBC_1.3-10.tar.gz

That got RODBC installed. Now, I'm trying to connect to the database by running this syntax (as described online and in the package's documentation):

odbcConnect(dsn, uid = "", pwd = "", ...)

When I do that, I get the following error:

Warning messages: 1: In odbcDriverConnect("DSN=xyz;UID=xyz;PWD=xyz") : [RODBC] ERROR: state IM012, code 0, message [unixODBC][Driver Manager]DRIVER keyword syntax error 2: In odbcDriverConnect("DSN=xyz;UID=xyz;PWD=xyz") : ODBC connection failed

So, it seems like the syntax is wrong, but I don't see where. R documentation says that Error IM012 is a syntax error (note: replaced 'XYZ' for actual credentials) I have a few questions:

First:

On MySQL Workbench, I input the following parameters to access the database: 1. Connection Name: a made up name for the database 2. Hostname: an amazon cloud database link 3. Port (leave default of 3306) 4. Username 5. Password

Where in the RODBC syntax of odbcConnect(dsn, uid = "", pwd = "", ...) do you put the location of the database, presumably an ip or link? I tried that for the DNS, and it didn't work (i.e. got the same syntax error).

Second:

and most importantly to answer my question:

Why am I getting this IM012 syntax error? What would the correct syntax be? Where in this syntax is there room to tell the program where the database is located?

Thanks in advance for your help!

Make SQL Query from button via PHP

I have my SQL query working fine in PHPMyAdmin but can't get it work with PHP. Let's say I have this query:

SELECT Login, Firstname, Lastname, Company INTO OUTFILE 'c:\\users.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'  
FROM `Users` WHERE Company = 'SID';

I would like to call my query from a web page with a click. I did 2 files fonction.php and form.php as follow. For fonction.php

<?php
function connectMaBase(){
    $base = mysql_connect ('localhost', 'root', '');  
    mysql_select_db ('CCPayment', $base) ;
}
?>

And for form.php I have this

<?php
include("fonction.php");
?>
<html>
    <head><title>Pages de requetes SQL </title></head>
    <body>
        <h1> Requete SQL </h1>
       <form name="sqlquery" method="post" action="form.php">        
        <input type="submit" name="valider" value="Go"/>
       </form>
 <?php 
    if (isset ($_POST['valider'])){
        connectMaBase();
        $sql = "SELECT Login, Firstname, Lastname, Company  INTO OUTFILE \'c:\\\\Users.txt\' FIELDS TERMINATED BY \',\' LINES TERMINATED BY \'\\r\\n\' \n"
     . "FROM `OptiOutput` WHERE Company = \'SID\'";

        mysql_query ($sql) or die ('Erreur SQL !'.$sql.'<br />'.mysql_error()); 
        mysql_close();
    }
?>
    </body>
</html>

I'm a beginner and could not find what is wrong in the code.

Thanks for your help.

Finding Reccurring Number Combinations in Column of Numbers

I have searched and found discussions and solutions to similar problems, but not quite or as complex as I'm trying to figure out.

I have an access table which consists of two columns Draw Number and Number Drawn as shown below. Draw Number is repeated 20 times, to correspond to the 20 numbers that are drawn in each particular draw.

I'm trying to figure a way to determine the most frequent occurring combination of numbers (5 numbers) for all of the draws in each of the 20 number sets. So for instance, 12341 occurs n x, 12342 occurs nx, 12343 occurs n x, etc.

I've created parameter queries which allow me to search for different number combinations from 2 to 10 numbers, and they work OK returning the number of occurrences of a combination of numbers that I input through a simple UI. But the goal is to figure out pragmatically what the optimum combination of numbers.

Hope this makes sense. And by the way, there are 36 million or so rows in the table. The para queries work quite well however; it takes just over a second to return results for each number added. So, query two numbers = 2 second wait, three numbers = 3 second wait, etc.

I've been thinking about a loop of some type but don't know how to get started? Processing time isn't an issue; can take a day if required!

This is written in VBA and has an assortment of queries, temp tables, etc to get the job done.

DRAW NUMBER NUMBER DRAWN
1   1
1   28
1   19
1   3
1   38
1   46
1   43
1   29
1   13
1   22
1   20
1   11
1   50
1   51
1   53
1   54
1   57
1   64
1   76
1   78
2   29
2   14
2   2
2   1
2   35
2   40
2   39
2   30
2   10
2   27
2   21
2   6
2   42
2   50
2   51
2   53
2   54
2   61
2   65
2   69

why we provide alias names after 'DELETE' in multiple delete

This might be a very stupid questions, but I am keen to know if anyone has any suggestions:

Single Table Delete Correct Query : delete from vehicle_owner where id=3;

Single Table Delete InCorrect Query : delete from vehicle_owner v where v.id=3

Mulitple Table Delete : delete v,s from vehicle v , category s where v.id=3 and v.id=s.id;

Qs 1: I was wondering that why 1st is right and why 2nd is incorrect. Basically I am looking for logical answers which explains why providing an alias in delete query is incorrect.

Qs 2: why we keep two alias names after 'DELETE' keyword for multiple delete. Anyways we are providing complete details in join condition. So why it is designed in such a way.

Angular does not updated loop due to implementation $mdDialog (Material Design)

Based on my previous post: Angular loop is not updating, i updated the code slightly to add a dialog box to the button a a user.

the only change in my app.js file is the directive to add a $mdDialog box. so the complete code for my app.js file is:

var app = angular.module('AddUser', ['ngMaterial']);

app.controller('AppCtrl', function($scope, $http, $mdDialog){
$scope.userInfo =  [];

/** function to add details for a user in mysql referecing php **/
$scope.save_user = function() {
    $http.post('db.php?action=add_user', 
        {
            'user_name'  : $scope.user_name, 
            'user_email' : $scope.user_email
        }
    )

    .success(function (data, status, headers, config) {
        $scope.userInfo.push(data);
        $scope.get_user(); //this will fetch latest record from DB
        console.log("The user has been added successfully to the DB");
        console.log(data);
    })

    .error(function(data, status, headers, config) {
        console.log("Failed to add the user to DB");
    });
}

/** function to get info of user added in mysql referencing php **/
$scope.get_user = function() {
    $http.get('db.php?action=get_user').success(function(data)
    { 
        $scope.userInfo = data;   
        console.log("You were succesfull in showing user info"); 
        //$scope.get_user(); //this will fetch latest record from DB
    })
}

/** function to delete a user from list referencing php **/
$scope.delete_user = function(index) {  
    $http.post('db.php?action=delete_user', 
        {
            'user_index' : index
        }
    )      
    .success(function (data, status, headers, config) {    
        $scope.get_user();//this will fetch latest record from DB
        console.log('Deletion was succesfull');
    })

    .error(function(data, status, headers, config) {
       console.log("You were NOT succesfull in deleting a user"); 
    });
}

$scope.showPopUp= function(ev) {
    $mdDialog.show({
      controller: DialogController,
      templateUrl: 'popup.tmpl.html',
      parent: angular.element(document.body),
      targetEvent: ev,
    })
};

function DialogController($scope, $mdDialog) {
    $scope.closeDialog = function() {
      $mdDialog.hide();
    }

    $scope.save_user = function() {
        $mdDialog.hide();
    }
}

});

i had to comment out the following code in the get_user function ($scope.get_user();) otherwise i could not preform a delete but then on the other hand the loop did not update. so either i comment out $scope.get_user() in de get_user function and then i can delete a user or i don't comment it out and the loop will update when i add a new user but then the delete function does not work ...

my html code is:

<body ng-controller="AppCtrl">
  <div>
    <ul ng-init="get_user()">
      <li ng-repeat="user in userInfo ">{{user.user_name}}<a href="#" ng- 
      click="delete_user(user.id)"> --> Delete</a></li>
    </ul>
  </div>

 <md-button class="md-primary md-raised" ng-click="showPopUp($event)" flex 
 flex-md="100">Add a user</md-button>
</body>

and the code for the dialog box (popup.tmpl.html) is the following:

<md-dialog aria-label="Add or invite a user" ng-app="AddUser">
<form ng-controller="AppCtrl">
      <md-toolbar>
       <div class="md-toolbar-tools">
         <h2>Add or invite a user</h2>
         <span flex></span>
         <md-button class="md-icon-button" ng-click="closeDialog()">
              <md-icon md-svg-src="images/ic_close_24px.svg" aria-label="Close 
              dialog"></md-icon>
         </md-button>
       </div>
      </md-toolbar>
      <md-dialog-content>
        <div>
          <div layout="" layout-sm="column">
            <md-input-container flex="">
            <label>Enter a name</label>
            <input name="user_email" ng-model="user_name">
            </md-input-container>
        </div>
        <div layout="" layout-sm="column">
          <md-input-container flex="">
          <label>Enter an e-mail</label>
          <input name="user_name" ng-model="user_email">
          </md-input-container>
       </div>
      </div>
     </md-dial
     <div class="md-actions" layout="row">
       <md-button ng-click="closeDialog()" class="md-primary">Cancel</md-     
        button>
       <md-button ng-click="save_user()" name="add_user" class="md-
       primary">Add</md-button>
     </div>
    </form>
   </md-dialog>

how to go back to login page if given wrong php GET credentials?

so im given 3 variables on my login page from an outside source, if one of those do not belong in the database I want it to just go to the normal login.php page. as of right now it stays on that page and does not change the url even though the vars are not in the db. i give it localhost/john/login.php?uniqueID=BmWDLlkcyU&compID=2&tempID=22 , but tempID 22 does not exist so i want it to revert to login.php

     $uniqueID = $_GET['uniqueID'];
     $compid =  $_GET['compID'];
     $tempID = $_GET['tempID'];

     $checkUnique = mysqli_query($conn, "SELECT unique_id from answers WHERE unique_id = '$uniqueID' and template_id = '$tempID'");
     $checkComp = mysqli_query($conn, "SELECT company_id from t_list WHERE company_id = '$compid'");


 if(!$checkUnique)
{

   header("Location: login.php"); 
  exit;
 }
else if(!$checkComp)
  {
    header("Location: login.php"); 
   exit;
   }

Alternative To Huge Result Set From Multiple LEFT JOINs

I'm seeking some advice on handling scenarios where the number of rows in a result set explodes when you LEFT JOIN multiple tables. I understand this is expected behavior, but I’d like to know the recommended way to handle it. I know I can have my application perform multiple queries to reduce the number of rows returned overall, but I am trying to see if there is a way to have SQL do most of the heavy lifting and still make only one “round trip” (e.g., a la this answer).

Example

Here’s my SQL:

SELECT al.title albumTitle
    , releaseDate
    , name artistName
    , duration
    , t.title trackTitle
    , styleName
FROM album al
LEFT JOIN lu_albumartist aa ON aa.albumId = al.albumId
LEFT JOIN artist ar ON ar.artistId = aa.artistId
LEFT JOIN track t ON t.albumId = al.albumId
LEFT JOIN lu_albumstyle ast ON ast.albumId = al.albumId
LEFT JOIN style s ON s.styleId = ast.styleId
WHERE al.title LIKE '%A Love Supreme%'

This SQL Fiddle helps demonstrate the problem:

I am retrieving information on a music album. I really only need 11 rows to be able to have my app populate all fields (1 album, 4 artists, 3 tracks, 3 styles), but the query pulls back 36 rows. I won't be able to use most rows; for example, I don’t care about all the permutations of styles & artists or styles & tracks. When I add even more LEFT JOINS for other things (e.g., instruments, formats, comments, track play info, etc.) the number of permutations can expand into the 10,000s!

Ideally, what I'd like is a result set that’s more concise:

Super Compact: 4 rows (doesn't make much sense to read the table this way, though the application could parse it)

|          title |                releaseDate |           name |    duration |                                  title |   styleName |
|----------------|----------------------------|----------------|-------------|----------------------------------------|-------------|
| A Love Supreme |                       1965 |  John Coltrane |         479 |               Part I - Acknowledgement |   Free Jazz |
|    [something] |                [something] |    McCoy Tyner |         435 |                   Part II - Resolution |    Hard Bop |
|    [something] |                [something] | Jimmy Garrison |        1060 | Part III - Pursuance / Part IV - Psalm |       Modal |
|    [something] |                [something] |    Elvin Jones | [something] |                            [something] | [something] |

..."[something]" just means the value can be anything; the app won't care

Compact: 11 rows (makes sense to humans)

|          title |                releaseDate |           name | duration |                                  title | styleName |
|----------------|----------------------------|----------------|----------|----------------------------------------|-----------|
| A Love Supreme |                       1965 |                |          |                                        |           |
|                |                            |  John Coltrane |          |                                        |           |
|                |                            |    McCoy Tyner |          |                                        |           |
|                |                            | Jimmy Garrison |          |                                        |           |
|                |                            |    Elvin Jones |          |                                        |           |
|                |                            |                |      479 |               Part I - Acknowledgement |           |
|                |                            |                |      435 |                   Part II - Resolution |           |
|                |                            |                |     1060 | Part III - Pursuance / Part IV - Psalm |           |
|                |                            |                |          |                                        | Free Jazz |
|                |                            |                |          |                                        | Hard Bop  |
|                |                            |                |          |                                        |     Modal |

I'm less concerned about performance than my app's code portability, readability, & scalability.

Based on that earlier linked question, the other answers and comments suggested I shouldn’t attempt to join more than 3 tables.

1. Should I just give up on this approach? If I were to use multiple queries, there could be a dozen or more.
2. I suspect there is a way to solve this with UNION ALLs, but is that the best practice?
3. I'm guessing this is a relatively common problem, but I couldn't find a good answer or a set of guidelines to follow. What’s the recommended approach for this situation?

MySQL subtract two columns and compare them to number in WHERE clause

I have a table which looks like this:

id | some | irrelevant | data | time | waste_time | logged_in
--------------------------------------------------------------
1  |      |            |      | 0    | 246        | 0
2  |      |            |      | 0    | 360        | 0
3  |      |            |      | 116  | 116        | 0
4  |      |            |      | 291  | 199        | 0
5  |      |            |      | 3997 | 520        | 0
6  |      |            |      | 0    | 175        | 0
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

I am trying to have multiple tables, based on the result of time-waste_time I tought I had it work, but I was wrong. If the result is negative number, the compare to 0 (time-waste_time > 0) just gets ignored.

Those are my queries:

SELECT * FROM players WHERE time-waste_time>0 OR logged_in=1 ORDER BY logged_in DESC, login DESC

SELECT * FROM players WHERE time-waste_time>0 AND login<$time_limit ORDER BY login DESC

SELECT * FROM players WHERE time-waste_time<=0 AND logged_in=0 ORDER BY login DESC

The first query will show player that has time 0 and waste_time 150, even tho 0-150 = -150 and -150 < 0 and the third query will not show such player. Re required result is, that such player will not be shown with first and second query, but will be shown with third one.

I already found this anwer: Using 'Greater than' operator with a negative number but its not helping.

Any ideas why its acting like this and how can I make it work?

Ps.: logged_in IS ZERO

Thanks.

Statement Passed to Mysql Database not filling DataTable

 dbQuery = "SELECT * FROM table WHERE date BETWEEN " & startDate & " AND " & endDate & ""

When i execute this statement in NaviCat it does not return any values either. Tho the values do exist on the 19th and 22nd of June in 2015. am i sending the query wrong???

Upon debugging the query is sent as follows.

select * from table where date between 6/1/2015 and 6/31/2015

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?

Error Code: 1044. Access denied for user

While logging into MySQL using 'root'@10.0.1.15'

I tried to grant a user SELECT, INSERT, UPDATE but got this error from MySQL

Error Code: 1044. Access denied for user 'root'@'10.%' to database 'abc'

This is the query that I used to grant

GRANT SELECT, INSERT, UPDATE ON abc.* TO 'myUser'@'10.%';

This makes no since because when I execute SHOW GRANTS FOR 'root'@'10.%' Here is what I get

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.%' IDENTIFIED BY PASSWORD 'jklasdfksfkashdfksdfhsdlkfasdfjklasdfsjk'

I tried to do grant all privileges all over again but still getting the same error.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.%';
FLUSH PRIVILEGES;

What I could be doing wrong here? why aren't the user 'root'@'10.0.1.15' able to GRANT other users privileges?

Echo result of sql query in php

I have this sql query and I am trying to print / get the result.

$sql2="SELECT COUNT(*) FROM zones where routeID='15'";

The result is 3. How can I get this on my screen??

Database saving strange characters despite character set, and data sent, being in UTF8

When I save certain characters to my MariaDB database, they are being stored as a string of 2-4 characters.

For example, ™ is saved in the database as â„¢.

From the head of the html page that contains the form I have

<meta charset="utf-8">

When I check the headers sent to the server, it shows

Content-Type:text/html; charset=UTF-8

The PHP file handling the form data has

$mysqli->set_charset('utf8');

I've converted the database and table to UTF8, by doing:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Checking the database and table details in information_schema table I can see that the database and table character set is UTF8 and default collation name is utf8_general_ci.

But the wrongly encoded characters are still being saved to the db.

I'm viewing the table data with terminal, so there should be no issue with the encoding on the viewing side.

What else am I missing?