1 . How to access my MySQL files on a hard drive using Windows?

Answer :

You've figured out one possible way. The other way is to find My.ini (mysql config file) in which you will find datadir and basedir like

# Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.7/"

# Path to the database root
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

change the datadir to whatever location you have your MySQL data and restart your server. Since your current data dir is "c:\mamp\db\mysql" look for that line in the config file.

if you are going to use it often, i'd suggest installing MySQL server, workbench and mysql notifier using mysql installer for windows. It's much more easier to manage. With workbench you can do many things.


Leave a Comment

Name  
  Email   
Message
1 . Calculating difference on datetime row betwen rows on the same table?

Answer :

Assuming there is always a start for each pause and end, wouldn't something like this be more direct?

SELECT t.task
   , SUM(TO_SECONDS(t.stime) 
         * CASE WHEN t.status IN (1) THEN -1
                WHEN t.status IN (2, 3) THEN 1
                ELSE 0
           END
     ) AS totalTimeSecs
FROM tblwork AS task
GROUP BY t.task

I'm not quite sure offhand how big the values that come out of TO_SECONDS() are for current timestamps; but if they are an issue when being summed, if could be changed to

   , SUM((TO_SECONDS(t.stime) - some_constant_just_before_or_at_your_earliest_seconds)
         * CASE WHEN t.status IN (1) THEN -1
                WHEN t.status IN (2, 3) THEN 1
                ELSE 0
           END
     ) AS totalTimeSecs

You can detect "abnormal" data by adding the following to the select expression list

, CASE WHEN SUM(CASE 
                WHEN t.status IN (1) THEN -1 
                WHEN t.status IN (2, 3) THEN 1 
                ELSE 0 END
              ) = 0 
       THEN 'OK' 
       ELSE 'ABNORMAL' 
   END AS integrityCheck

Note: any "unclosed" intervals will be marked as abnormal; without much more complicated and expensive start and end checking for intervals to differentiate "open" from "invalid", it's probably the best that can be done. The sum used for additonal "integrityCheck" equaling -1 might hint at an open ended interval, but could also indicate an erroneous double-start.


2 . Does Knex.js prevent sql injection?

Answer :

In that case parameters and query string are passed separately to database driver protecting query from SQL injection.

Other query builder methods always uses binding format internally so they are safe too.

To see how certain query is passed to database driver one can do:

knex('foo').where('id', 1).toSQL().toNative()

Which will output SQL string and bindings that are given to driver for running the query.

Biggest mistake that one can do with knex raw queries is to use javascript template string and interpolate variables directly to SQL string format like:

knex.raw(`select * from foo where id = ${id}`) // NEVER DO THIS 

One thing to note is that knex table/identifier names cannot be passed as bindings to driver, so with those one should be extra careful to not read table / column names from user and use them without properly validating them first.


3 . Dropping a table in mySQLi db if contains value in name?

Answer :

Referring to this SO Answer, you can filter tables names from information_schema.tables table like this:

SELECT table_name FROM information_schema.tables WHERE table_name like '%hello%';

So you can create a statement englobing the previous select in this way:

DELIMITER $$
SET @s = SELECT CONCAT('DROP TABLE ',table_name,';') 
         FROM   information_schema.tables 
         WHERE table_name like '%hello%'$$
DELIMITER ;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

EDIT:

The question has changed asking for a PHP routine to drop tables. In PHP you should run the first SQL to get table names, then delete with a loop those tables:

$tableString = "hello";

$sql = "SELECT table_name FROM information_schema.tables WHERE table_name like '%$tableString%'";

$res = mysqli_query($conn, $sql);
while ($row = mysqli_fetch_array($res)) {
    $tname = $row[0];
    $result = mysqli_query($conn, "DROP TABLE $tname");
    if (!$result) echo "Table $tname deletion unsuccessfully<br> ";  
}

4 . How can I get ids of grouped rows?

Answer :
SELECT t.*
  FROM notifications t
 INNER JOIN (SELECT s.post_id, s.user_id
               FROM notifications s
              WHERE belongs_to_user_id = 101
              GROUP BY post_id, user_id
              ORDER BY post_id DESC, user_id DESC
              LIMIT 3) u
    ON u.post_id = t.post_id
   AND u.user_id = t.user_id
 WHERE t.belongs_to_user_id = 101
 ORDER BY t.id

Update: same query using DISTINCT in the subquery:

SELECT t.*
  FROM notifications t
 INNER JOIN (SELECT DISTINCT s.post_id, s.user_id
               FROM notifications s
              WHERE belongs_to_user_id = 101
              ORDER BY post_id DESC, user_id DESC
              LIMIT 3) u
    ON u.post_id = t.post_id
   AND u.user_id = t.user_id
 WHERE t.belongs_to_user_id = 101
 ORDER BY t.id

5 . SQL query sort by count() from a different table?

Answer :
SELECT w.*
  FROM websites w
  LEFT OUTER JOIN (SELECT website_url, COUNT(*) as num_articles
                     FROM articles
                    WHERE is_valid='1'
                    GROUP BY website_url) a
    ON a.website_url = w.url
 WHERE (w.name LIKE '%xx%' OR w.url LIKE '%xx%' OR w.country LIKE '%xx%')
 ORDER BY a.num_articles DESC
 LIMIT 0,10


6 . Sql bindParam not Working?

Answer :

In the bindValue() code, your loop is wrong. You should be looping over the array $row It should be

foreach($row as $value){
   echo "<td>". $value['content'] . "</td>";
}

7 . Drupal sql conditional clauses for multiple tables?

Answer :

You cannot chain leftJoin (or any join) in Drupal queries, because leftJoin() returns the alias, not the query. Then you should use execute() to "run" the query.

$or = db_or()
  ->condition('fd.field_detailed_question_value', '%'.db_like($term ).'%', 'LIKE')
  ->condition('fb.body_value','%'.db_like($term ).'%' , 'LIKE');

$query = db_select('node', 'n');
$query->fields('n');
$query->leftJoin('field_data_body' , 'fb', 'fb.entity_id=n.nid');
$query->leftJoin('field_data_field_detailed_question' ,'fd', 'fd.entity_id=n.nid');
$query->condition($or);
$stmt = $query->execute(); // execute the query (returns the "statement" to fetch).

while ($row = $stmt->fetchObject()) {
    //..
}

You have to add fields :

$query = db_select('node', 'n')->fields('n');

or

$query = db_select('node', 'n')
         ->addField('n','title')
         ->addField('n','nid');

8 . MySQL Query to “flatten” or pivot table by fields?

Answer :

You are using group by without aggregation function and for this you have unpredictable result you should use a (fake) aggregation function

  SELECT group_id, form_id,
    min((case WHEN element_label = 1 THEN `element_value` END))  first_name,
    min((case WHEN element_label = 3 THEN `element_value` END)) last_name,
    min((case WHEN element_label = 4 THEN `element_value` END)) school_email,
    min((case WHEN element_label = 5 THEN `element_value` END)) contact_email
  FROM `wp_formmaker_submits` 

  WHERE `form_id` = 12
  GROUP BY group_id, form_id

The use of group by without aggregation function is deprecated in sql and not allowed in the most recent version of mysql and in most of DB .. in this case the result for column not in aggreated result is unpredicable tipically is the first value encountered by sql engine for these columns


9 . Query with GROUP_CONCAT not working with PHP?

Answer :

The problem is the PHP. Try replacing:

     echo $row['start_date'];

with:

     echo $row['data'];

You need to use the column alias that you assigned in the SELECT.


10 . MYSQL - Select formatted date and also count the formatted date?

Answer :

You need to group by the formatted time, not the time in the table, so that all the times with the same hour will be grouped together in the count.

GROUP BY Time

Or, since the formatted time is the same as the hour of the table column, you can do:

GROUP BY HOUR(connectedOn)

BTW, there's no need to use DISTINCT when using GROUP BY. Grouping prevents duplicates. And the fact that you used DISTINCT as if it's a function suggests that you don't understand how it works. It's not a function that applies to a specific column, it's a keyword that applies to the entire SELECTlist.



11 . How to update date in sql?

Answer :

Don't put SET every time before each column, just use SET keyword once.

update vehicles 
set capacity = "+capacity+", 
    brand ='"+brand+"', 
    type='"+type+"', 
    model='"+model+"', 
    dateAqcuire ="+date+" 
where registrationPlate ='"+registrationNumber+"'

General Syntax: The syntax for the MariaDB UPDATE statement when updating one table is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];