Hot answers tagged

22

Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to: SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as ...


4

SHOW GRANTS only gives you back whatever you are connected as, which was root@localhost. root@localhost and root@'%' and completely different users. Just do SHOW GRANTS FOR root@localhost; SHOW GRANTS FOR root@'%'; root@localhost lets you connect from the DB Server via mysql.sock (the socket file) root@'%' lets you connect via TCP/IP, but you must ...


3

You can try this with UNION ALL as probably you need duplicate entry too as will adding table name it will be unique and performance will be also improved SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as TableName FROM tablename1 where Active =1 UNION ALL SELECT Id, productName, Largeimagepath, Discount, Price, Image, '...


3

The column that you have is a VARCHAR so the ordering applied by the RDBMS will be alphabetical instead of numeric. What is the smallest possible unit of storage for a HDD these days? GB? One way to solve your problem would be to create another column that transforms the storage into GB (or some other unit). Make sure that this column is integer or numeric ...


3

The GROUP_CONCAT allows this form of transformation: SELECT register_cr.id, register_cr.first_name, register_cr.father_name, register_cr.surname, register_cr.address, GROUP_CONCAT(DISTINCT cr_record.charge SEPARATOR "\n"), GROUP_CONCAT(DISTINCT cr_record....


3

Perhaps this can lead to some SELECTs to solve your problem? For the 'ana_index': mysql> SHOW GRANTS FOR ana_index@localhost; | GRANT USAGE ON *.* TO 'ana_index'@'localhost' IDENTIFIED BY PASSWORD '*...' | GRANT ALL PRIVILEGES ON `test`.* TO 'ana_index'@'localhost' These tables were populated: mysql> SELECT * FROM mysql.user WHERE user = 'ana_index'...


3

Something like this would give you the total across all three of those days: SELECT COUNT(attendance.AttendanceID) as 'Total Attendance' FROM student INNER JOIN attendance ON attendance.StudentID = student.StudentID AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015') WHERE student.WorkshopID = '101' If you wanted the total separately for ...


3

Wrap the selects into parentheses (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC ) UNION (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) ORDER BY MONTH(`Date`) ASC)


2

No. Don't put multiple values in a column; make a new table: Mapping table -id -- same as the `id` in transaction_tbl -ref_number PRIMARY KEY(id, ref_number) For your example, there will be 3 rows in this table for the 1 row in transaction_tbl. INSERT INTO transaction_tbl (name, descripton) VALUES (..., ...) $id = LAST_INSERT_ID() INSERT INTO mapping (id,...


2

MAMP seems to handle the root username differently than WAMP. I had to include the root password as 'root' in my PHP script, instead of leaving it blank, as such: $link = mysqli_connect('localhost', 'root', 'root');


2

You are getting this error because the root account on your local machine doesn't have a password. That's why you can login locally with using password: NO Probably your server on godaddy has a password set, or the root account disabled. You need to fix the credentials in your application (and probably use a login other than root)


2

There is few ways to do it as per below- 1) partitioning but you don't want it. Also need to remember that partitioning does not support with foreign keys. 2) You can normalize your table if there is duplicate values in table. 3) If this table contains transactional data or logs then you can move old data in historical tables year wise etc if possible in ...


2

You're getting all the students at once and just displaying the table, without any subselection. Your second SQL Statement here: <?php $sql1 = "SELECT std_result.id,std_result.SubjectId,std_subject.Subject,std_result.TermOneWritten,std_result.TermOneOral,std_result.TermTwoWritten,std_result.TermTwoOral FROM std_result INNER JOIN std_subject ON ...


2

1GB, 100TB, etc are just strings of text with no arithmetic meaning regarding which is greater. I'm assuming your column only has ONE value, like 1GB or 500GB, and not all of those values I think you'd have to attempt something like using REPLACE(hdd,'GB',000000) or REPLACE(hdd,'TB',000000000) and using numeric comparisons in your between. It's definitely ...


1

The bind_param page mentions (emphasis mine) for the types parameter that corresponding variable is a blob and will be sent in packets which is done in this manner: $null = NULL; $stmt->bind_param('sbiiissi', $input[1], $null, $input[3], $input[4], $input[5], $input[6], $DateAdded, $_SESSION[$thisTracker]); $stmt->send_long_data(...


1

(This is why we asked for the datatype of date_created!) Assuming date_created is a TIMESTAMP: 2018-11-11 is an arithmetic expression that evaluates to 1996; certainly not what you wanted. "2018-11-11" (with quotes) will compare correctly to 1446976737. UNIX_TIMESTAMP(ts_column) will generate something like 1446976737, so it should work correctly. But ...


1

You need to change the COUNT() so it only counts the child records (and not always the master). SELECT Activity.name, COUNT(Notifications.ActId) from Activity LEFT JOIN Notifications on Notifications.ActId = Activity.ActId GROUP BY Activity.name COUNT() won't count NULL values (from your LEFT JOIN), so you will only count records from the Notifications ...


1

1) How can it be? If you check the documentation for server status variables, this number (3551496) is: The number of connection attempts (successful or not) to the MySQL server. i.e. the number of connections (plus attempts to connect) since the server was booted! The number of active connections can be verified by looking at the Threads_connected ...


1

You can do it by running the following query: SELECT a.sender,a.status,a.type,c.p_id,c.p_name,c.st FROM a INNER JOIN b ON a.sender=b.sender INNER JOIN c ON b.p_id = c.p_id WHERE a.status='approved' AND c.st='current';


1

SET foreign_key_checks = 0; //import data SET foreign_key_checks = 1;


1

You could write a simple query using OR in WHERE clause and passing your search string. SELECT school_name FROM schools WHERE Subject1 = 'Physics' OR Subject2 = 'Physics' OR Subject3 = 'Physics' OR Subject4 = 'Physics' You can parameterize the search string and pass on to this query.


1

Use your application code to extract "phrases" and store them in a table. Then do SELECT ... GROUP BY ... to generate the counts in question. No, it is not practical to do this entirely in SQL. SQL is limited in its text parsing ability.


1

Another possibility is some failure of mysql server to deliver the result, but not severing the connection before the next query is run. Came across this with mysql server 8.0. The error log showed an out of memory error for the sort buffer size. After increasing that, these errors in the application went away.


1

Here is the table structure CREATE TABLE `users` ( `userid` INT NOT NULL AUTO_INCREMENT, `user_name` VARCHAR(50) NOT NULL, user_type int 1, `email` VARCHAR(100) NOT NULL, PRIMARY KEY (`userid`) ); CREATE TABLE `user_type` ( `user_typeid` INT NOT NULL AUTO_INCREMENT, `user_type` VARCHAR(50), PRIMARY KEY (`user_typeid`) ); CREATE TABLE `...


1

Going off the links that you provided, I would suggest looking into PHP's PDO (PHP Data Object). The reason for this is because the mysqli way is getting pretty old at this point. PDO is relatively simple to use and it has different drivers for different database vendors. So if you want to switch out your database system for something else, then it is ...


1

In theory it's possible. What you call a table in InnoDB is a B+tree index named PRIMARY. The key in the PRIMARY index is the primary key field(s). All secondary indexes are B+tree indexes, too. The key would be the secondary index field(s). The value is the primary key. (See my slides to get more details http://www.slideshare.net/akuzminsky/indexes-in-my-...


1

As explained in the documentation, there are rules on what makes a valid identifier. This rule is what affects your case: Identifiers may begin with a digit but unless quoted may not consist solely of digits. So, as @ypercube says, you need to quote your table name, either with double quotes or backticks: SELECT * FROM "2521118" ORDER BY id DESC LIMIT ...


1

If you want to DELETE all the information in table1.*: DELETE table1.* FROM test.table1 JOIN test.table2 ON (table2.id = table1.id) WHERE table1.price < (table2.price*2); This will DELETE all rows from table1 that match with the WHERE criteria. If you want to DELETE all the rows that match against the WHERE criteria in table2, just replace DELETE table1....


1

Since you said you can only use the Command prompt, my suggestion is to shift the character set to one that supports Arabic within the mysql client: I just ran these commands on my laptop at home (Windows 8.1) mysql> select * from information_schema.character_sets where description like '%arabic%'; +--------------------+----------------------+-----------...


Only top voted, non community-wiki answers of a minimum length are eligible