what is the difference between GROUP BY and ORDER BY in sql
When do you use which in general? Examples are highly encouraged! I am referring so MySql, but can't image concept being different on another DBMS ...
mysql
MySQL Result Array in PHP
Let's say I have this query in PHP. $a = mysql_query("SELECT * FROM `users` WHERE `id` = '1' "); Which is nice and all, but I have a problem. How do I view the contents of $a, which I presume is an array like so: Index | Value ID | 1 Username | Foo Password | Bar ... ...
Extremely basic PHP and Mysql
Background: I am more of a designer than a programmer, but have hacked templates for many open source CMS's (Drupal, Joomla, Wordpress) I want to start from scratch in regards to the relations of php and a mysql database. Lets assume I have a working database and php engine locally. What would be my first step to connecting to my data...
Getting Data From Many Different MySQL Tables
I have 6 tables with exactly the same structure, the field names go like this: country | incoming | outgoing_national | outgoing_us | outgoing_uk | outgoing_global | outgoing_text I'm trying to code a rates table where the rates change instantly (jusing jQuery) when a country is selected from a dropdown. Because this is going to be ha...
generate weekly calendar in mysql/php
i'm developing a read-only weekly calendar view of users's events. Columns are the days of the week (mon > sun) Rows are timeslots (8:00>9:00, 9:00>10:00... from 8AM up to 7PM) Question: what's the best approach to generate that user calendar: Option 1: pure SQL I don't even know if that is possible, but i would find it supremelly eleg...
MySQL: Searching a single table multiple times?
I have a table that's holding metadata for entrys from another table. The metadata-table looks like this (I removed relating Ids so it's more obvious): id entry_id property value 1 12 color blue 2 12 shape circle 3 13 color red 4 13 shape circle 5 14 color blue 6 14 shape square 7 15 material stone 8 12 material plastic Now I w...
MySQL indexes - how many are enough?
I'm trying to fine-tune my MySQL server so i check my settings, analyzing slow-query log, simplify my queries if possible. Sometimes enough if i indexing correctly sometimes not. I've read somewhere (pls correct me if this is stupidity) more indexes than i needed make the same effect like i haven't any of them. How many indexes are eno...
should i use insert select or just loop through with insert ?
i have extracted a list of usernames using the answers from this: http://stackoverflow.com/questions/1273259/how-to-optimize-mysql-query-if-i-have-too-many-or-operators-in-my-query now i have another issue. I have a friend table made up of just 2 columns, requestor and buddy both form a composite primary key. Now suppose i have an arr...
Understanding SQL REFERENCES
Recently I've asked a question about the best way to go to design a DB schema to support multiple types of users and interactions between them, one of the answers suggested that I use one table for each user type and Distributed Keys. The thing is the only databases I actively work with are MySQL and SQLite and I've always done this kin...
In MySQL, is it possible to get more than 1024 characters back from GROUP_CONCAT
I have a query which returns the count of a status that needs some very complex work to calculate. The user wants to drill down into that list, and I thought the easiest way would be to get the ids using GROUP_CONCAT. Unfortunately, the result from GROUP_CONCAT is being truncated to 1024 (?!) characters. Is it possible to get more than...
matching text to a row
I have a row in my mysql table like so John - Hey (johnnny) but because of how the string has been formatted i have john hey johnnny I've tried this query $q = mysqli_query($id,"SELECT * FROM mixes WHERE `tracklist` LIKE %".$_REQUEST['did']."%"); but this did not work? Any ideas? ...
Transaction ID in MySQL Trigger
Is there a way to retrieve a transaction ID inside a MySQL trigger? ...
handling two mysql connection in PHP
Hi, for a project I need to open a Mysql connection, close it and let old legacy code run after that. The legacy code have it's own connection, and just call mysql_query($sql) without the resource parameter. How can I handle this? Can I set a Mysql connexion as the global one? Must I re-execute the mysql_connect() statement? The legac...
Get details from another mysql table
Hi, I have a table which would contain information about a certain month, and one column in that row would have mysql row id's for another table in it to grab multiple information from is there a more efficent way to get the information than exploding the ids and doing seperate sql queryies on each... here is an example: Row ID | Name...
sql complex tree rows
i have following function with a query. it works fine with integer id, but if the id is varchar (ID: Xewi3adc), then it dont work. well i dont know how to make it work yet, i been making lots of modifications, but i could'nt get it work. DROP FUNCTION IF EXISTS album_tree_connect; DELIMITER $$ CREATE FUNCTION album_tree_connect(value...
Multiple Datacenters
I am finding a lack of information regarding handling multiple datacenters. What tools and techniques are available for taking advantage of multiple datacenters? A requirement is that the databases become consistent very quickly. ...
Django - SQL Query - Timestamp
Can anyone turn me to a tutorial, code or some kind of resource that will help me out with the following problem. I have a table in a mySQL database. It contains an ID, Timestamp, another ID and a value. I'm passing it the 'main' ID which can uniquely identify a piece of data. However, I want to do a time search on this piece of data(th...
How can I modify a size of column in a mysql table ?
I have created a table and accidentally put varchar length as 300 instead of 65353. How can I fix that? example would be appreciated. thansk ...
How do I fix "Symbol not found: _is_prefix" when compiling Perl's DBD::mysql?
First I wanted to build the DBD::mysql package. That kept failing because whatever make resulted in could not be loaded for the tests with a Symbol not found: _is_prefix. So I assumed that cpan might be a tad old. I know it's a random assumption, but cpan did tell me to install the latest Bundle::CPAN. Who's successfully installed eithe...
Whats the best way to output data from a database using php?
I'm relatively new to php, and I'm working on a project using a mysql database. The project consists of users being able to write posts, which are then shown in a list format. The problem is, the posts are shown in different locations on the site, like the index (main) page, and the users profile page. Similar to twitter if you're confus...