I am trying to use PHP to display a table on a page. My select statement consists of a join of two tables and each table contains the columns called "id", where one table's "id" column is joined via the other table's "project_id" column.  i.e. Table A A.id, A.description  Table B B.id, B.project_id  WHERE A.id = B.project_id  My problem is that when I try to display both the "project_id" column and the "B.id" column (without aliasing the columns) it just automatically grabs the "A.id" column. I have aliased the table names, but not the column names, and I have tried using "B.id" instead of just "id" in the PHP code.  How do I grab the right data column?? I would like to know if there is another solution besides aliasing the columns, or if that is the proper way to deal with this situation.  Thank you
405
I have two tables in my database:  NEWS table with columns:   id - the news id  user - the user id of the author)   USERS table with columns:   id - the user id   I want to execute this SQL:   SELECT * FROM news JOIN users ON news.user = user.id    When I get the results in PHP I would like to get associative array and get column names by $row['column-name']. How do I get the news ID and the user ID, having the same column name?
67515
Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records.  A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column. This is where I am having problems.  data columns:  id, band_name, song_name, album_name, genre   MySQL statment:  SELECT * from us_music, de_music where `genre` = 'punk'   MySQL spits out this error:  #1052 - Column 'genre' in where clause is ambiguous   Obviously, I am doing this wrong. Anyone care to shed some light on this for me?
29248
Background  I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in ).  Question  Is there a way that I can incorporate this select statement into a script that dumps the queried data into a new database, such that the database could be installed on a new mysql server, and the statement would work with the new database. The new database should not contain records in addition to those that have been used in the query.  Update: For clarification, I am not interested in a csv dump of query results. What I need to be able to do is to dump the database subset so that it can be installed on another machine, and then the query itself can be reproducible (and modifiable with respect to the same dataset).   Example  For example, my analysis might query a subset of data that requires records from multiple (in this example 3) tables:  select table1.id, table1.level, table2.name, table2.level         from table1 join table2 on table1.id = table2.table1_id         join table3 on table3.id = table2.table3_id        where table3.name in ('fee', 'fi', 'fo', 'fum');
6130
In my custom Drupal 8 plug-in I perform such query:  $select = db_select('my_table', 'e'); $select-&gt;addField('e', 'field1');  $select-&gt;condition('field3', 'NULL', '!='); $select-&gt;condition('field4', array(11, 12, 31, 32, 33), 'IN');  $select-&gt;groupBy("e.field1"); $select-&gt;orderBy("e.field1"); $select-&gt;orderBy("e.field5"); $entries = $select-&gt;execute()-&gt;fetchAll(\PDO::FETCH_ASSOC);    And it works fine. It returns me all records grouped by field1 that meets the condition, orderered. But it returns only field1 column.  I wanted to get more columns, so I have added another $select-&gt;addField('e', 'field2'); just after first one:  $select = db_select('my_table', 'e'); $select-&gt;addField('e', 'field1'); $select-&gt;addField('e', 'field2');  $select-&gt;condition('field3', 'NULL', '!='); $select-&gt;condition('field4', array(11, 12, 31, 32, 33), 'IN');  $select-&gt;groupBy("e.field1"); $select-&gt;orderBy("e.field1"); $select-&gt;orderBy("e.field5"); $entries = $select-&gt;execute()-&gt;fetchAll(\PDO::FETCH_ASSOC);    This time I get error:     Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 Użyto 'my_database.e.field2' bez umieszczenia w group by:   Which means "my_database.e.field2 has been used without putting in into group by". But I don't want to group by every column I select.  Unfortunatelly Drupal 8 added ONLY_FULL_GROUP_BY :( Then, how can I use groupBy and select more then one column at the same time?
91184
I have following MySQL db structure:  content ======= id | tags --------- 1  | ,abc,def,ghi, 2  | ,abc,foo, 3  | NULL 4  | ,foo,bar,   categories ========== id -- 1 2   categories_to_content ===================== id | category_id | content_id -----------------------------  1 |           1 |          1  2 |           1 |          2  3 |           2 |          3  4 |           2 |          4   What I'm trying to do is to SELECT tags assigned to category and count them. Result for given schema:  tag | category | count ---------------------- abc |        1 |     2 def |        1 |     1 ghi |        1 |     1 foo |        1 |     1 foo |        2 |     1 bar |        2 |     1   Decomposition isn't possible (I hook up into existing CMS database).  My existing query:  SELECT tags, category_id as category FROM content JOIN categories_to_content ON content_id = content.id
65216
I am dissecting some code and came across this,   $sql = 'SELECT page.*, author.name AS author, updator.name AS updator '      . 'FROM '.TABLE_PREFIX.'page AS page '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS author ON author.id = page.created_by_id '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS updator ON updator.id = page.updated_by_id '      . 'WHERE slug = ? AND parent_id = ? AND (status_id='.Page::STATUS_REVIEWED.' OR status_id='.Page::STATUS_PUBLISHED.' OR status_id='.Page::STATUS_HIDDEN.')';   I am wondering what the "?" does in the WHERE statement.  Is it some sort of parameter holder?
95576
Supposing we have a table with four columns (a,b,c,d) of the same data type.  Is it possible to select all distinct values within the data in the columns and return them as a single column or do I have to create a function to achieve this?
5230
Here's my attempt at it:  $query = $database-&gt;prepare('SELECT * FROM table WHERE column LIKE "?%"');  $query-&gt;execute(array('value'));  while ($results = $query-&gt;fetch())  {     echo $results['column']; }
6305
I'm not used to the 'as' keyword and I thought it would be a nice idea to use it. So why do I get an 'unknown column' (eid) error? I use mysql...  select t1.index as eid, t1.col1 as col1, t1.col2 as col2,  t2.index, t2.col1, t2.col2, t3.index, t3.col1, t3.col2  from table1 t1  left join table2 t2 on t2.index=eid left join table3 t3 on t3.index=eid;   The full statement is too long to present here without boring everybody.  The column and table names are random.
39979
I have a simple problem when querying the SQL Server 2005 database. I have tables called Customer and Products (1->M). One customer has most 2 products. Instead of output as  CustomerName, ProductName ...  I like to output as   CustomerName, Product1Name, Product2Name ...  Could anybody help me?  Thanks!
30879
I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this?  EDIT: There are 53 columns in this table (NOT MY DESIGN)
31050
I need to run a query like:  SELECT p.id, p.name,         (SELECT name            FROM sites s           WHERE s.id = p.site_id) AS site_list   FROM publications p   But I'd like the sub-select to return a comma separated list, instead of a column of data.  Is this even possible, and if so, how?
48923
When using JDBC, I often come across constructs like    ResultSet rs = ps.executeQuery(); while (rs.next()) {     int id = rs.getInt(1);     // Some other actions }   I asked myself (and authors of code too) why not to use labels for retrieving column values:    int id = rs.getInt("CUSTOMER_ID");   The best explanation I've heard is something concerning performance. But actually, does it make processing extremely fast? I don't believe so, though I have never performed measurements. Even if retrieving by label would be a bit slower, nevertheless, it provide better readability and flexibility, in my opinion. So could someone give me good explanation of avoiding to retrieve column values by column index instead of column label? What are pros and cons of both approaches (maybe, concerning certain DBMS)?
32242
Table A (users)  id     | name   | ip 52433    Yashas   1.1.1.1 512      Admin    5.6.7.8   Table B (admin_record)  id    | admin | time    | date    | reason | action  52433   512     2312352   1213213   Test     WARN   id is FOREIGN key  Table B keeps a log of all actions an administrator takes on his users. And the table A stores the account information of the users &amp; administrators.  I am trying to display a list of actions taken by a administrators on a user.   I want to write a query which gets me all the rows of table B where id = fixed number.  Here is what I want to display:  "Administrator1 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX. "Administrator2 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX. "Administrator3 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.   All I have is the user's id using which I need to obtain all the entries in the log along with the administrator names. I need to obtain the admin's name, users name and all the contents of table B related to a particular user in one single query. If possible, I want the to change the field name(not literally change, but want the SQL server to send the data with new field names to separate admin name &amp; user name) of the admin's name to admin_name and user's name as . I mean something like this  SELECT users.name AS admin_name WHERE users.id = %i  AND SELECT users.name AS user_name WHERE users.id = %i   (the query isn't correct,it us just an example to show what I am trying)  Progress so far:   SELECT player.name, player.ip, admin.name, record.date, record.time, record.action, record.reason FROM admin_record AS record JOIN users AS player ON player.id = record.id JOIN users AS admin ON admin.id = record.admin WHERE player.id=%i   where admin_record is table B &amp; users is table A.
59811
Here is the script to create my tables:   CREATE TABLE clients (    client_i INT(11),    PRIMARY KEY (client_id) ); CREATE TABLE projects (    project_id INT(11) UNSIGNED,    client_id INT(11) UNSIGNED,    PRIMARY KEY (project_id) ); CREATE TABLE posts (    post_id INT(11) UNSIGNED,    project_id INT(11) UNSIGNED,    PRIMARY KEY (post_id) );   In my PHP code, when deleting a client, I want to delete all projects posts:  DELETE  FROM posts INNER JOIN projects ON projects.project_id = posts.project_id WHERE projects.client_id = :client_id;   The posts table does not have a foreign key client_id, only project_id. I want to delete the posts in projects that have the passed client_id.  This is not working right now because no posts are deleted.
35597
I'm setting up a database using phpMyAdmin. I have two tables (foo and bar), indexed on their primary keys. I am trying to create a relational table (foo_bar) between them, using their primary keys as foreign keys. I created these tables as MyISAM, but have since changed all three to InnoDB, because I read that MyISAM doesn't support foreign keys. All id fields are INT(11). When I choose the foo_bar table, click the &quot;relation view&quot; link, and try to set the FK columns to be database.foo.id and database.bar.id, it says &quot;No index defined!&quot; beside each column. What am I missing? Clarification/Update For the sake of simplicity, I want to keep using phpMyAdmin. I am currently using XAMPP, which is easy enough to let me focus on the PHP/CSS/Javascript, and it comes with phpMyAdmin. Also, although I haven't been able to set up explicit foreign keys yet, I do have a relational table and can perform joins like this: SELECT *  FROM foo  INNER JOIN foo_bar  ON foo.id = foo_bar.foo_id  INNER JOIN bar ON foo_bar.bar_id = bar.id;  It just makes me uncomfortable not to have the FKs explicitly defined in the database.
77591
I've created main SELECT statement like below. For every row in Base table (base on IDKey I've added columns with values from result of INNER JOIN of few tables.  SELECT   IDKey,    Name,    (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeX,    (select Amount from Table1 INNER JOIN Table2 ... where ID=IDKey) AS AmountTypeY FROM BaseTable   BaseTable:  IDKey Name  ---------- 1     Ian 2     John 3     Ann ...   INNER JOIN of few tables Table1, Table2... :   ID Amount Type -------------- 1  100    X 1  200    Y 2  300    X 2  400    Y 3  500    X 3  600    Y ...   Result looks like:  IDKey  Name  AmountTypeX  AmountTypeY ------------------------------------- 1      Ian   100          200 2      John  300          400 3      Ann   500          600 ...   Do you have any suggestion how to avoid this nested SELECT statements in the main query at the begining and made this query simpler and more effective ?  I wast thinking about PIVOTING of the INNER JOIN result to look like below (all data for particular ID in one row) and then JOIN with main query but Have no idea how to do this and is it possible.  ID  X   Y ----------- 1   100 200 2   300 400 3   500 600 ...