sql

how to connect two tables with sql and php

I am a bit new to sql and php so I need some help here. I created two tables and I want to establish a 'one-to-many relationship' but I can't figure out how I can give the data through the php file. Here is some code: CREATE TABLE `details` ( `details_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `latitude` ...

Reducing the number of queries from 2 to 1

Is it possible to use 1 query instead 2? Or this way is better? (for speed) <? $q1 = mysql_query("SELECT name,url,id FROM gallery") or die(mysql_error()); if (mysql_num_rows($q1) > 0) { while ($row = mysql_fetch_array($q1)) { echo 'IMAGES FROM '.$row['name']; $id = $row['id']; //second query for each gallery $imgq ...

SQL/PHP: Order by highest vote from different table.

I needed to recommend to the visitor similar items to the one they where looking at. So I asked in another question for a SQL query to get items with the same categories (from a 'categories' table). The next is the correct code I'm actually using: SELECT c2.itemID FROM categories c1 JOIN categories c2 ON c1.catID = c2.catID WHERE c1.ite...

Automate Generate Scripts Wizard in SQL2008

Problem: how to automate a Generate Scripts in SQL2008 Management Studio: ie right click on database, Tasks, Generate Scripts All Tables All Stored Procs Create drop statements Don't use USE statement Generate data This is a 20sec process to do by hand.. needs automating :-) I don't really want to code it in C# in SQL Server Manag...

SQL: check to see which id's already exist

Let's say I have a list of IDs and I want to see if rows already exist with those IDs in my SQL database. Is there a single SQL command that I can issue to get back the subset of the ID list that's not already represented in the database? I could iterate over each ID in my code and issue a separate SQL check for each ID, but that sounds ...

posting into two linked tables

I want to post something to my database which consists of two tables which are linked with a foreign key. I want to be able to post something to the first table which has only two cells (city and city_id) via php and then get redirected to the second table where someone can post detail about places of the city (houses squares ect (city_i...

Question: mysql Subqueries

If I run this query: SELECT `Manga`.`id`, `Manga`.`name` FROM `mangas` AS `Manga` WHERE `Manga`.`id` in ( SELECT `manga_id` FROM `mangas_genres` where `manga_id` = 1 ) My computer does not respond (I just mistype). It can't run this query. Is the above code invalid? Please, help me. I think it is valid, but it can't run. I don't und...

multiple rows or one collumn [sql performance question]

Hi everyone. I'm struggling with this simple idea. Look at reddit, when you sign up for a new account.. you automatically subscribe to some default channels. I want to do the same with my website. I know I could do the simple and stupid user_chan with id, user_id, chan_id if chan is like this : ID | NAME 1 | videos 2 | pictures u...

SQL Server query degrades over time from 0 to 60msec

Hi guys. I need some help in explaining this behavior in SQL Server 2008. I have a simple c++ program that is running a query in a loop. Here is the pseudocode of the program. myTempid = 0; while (1) { execQuery(select a.id from a,b,c,d where a.id = b.id and b.id = c.id and c.i...

How to perform a select in a single statement?

I have a Sql Server 2005 table with following data: idHearing is the primary key (identity), idCase is a foreign key that may duplicate. StartDate and StartTime specify the date and time of the event - both fields are of DateTime type (for whatever reason these are separate fields). All StartDate data has a time of 12:00:00. All St...

dump JDBC Derby database to a SQL script

Hello, I think this task should be obvious but I couldn't find how to do it. I am going to create a Java DB database (Derby) that will later be copied from my computer to another computer through a SQL script. Can I create it using Netbeans' GUI and then export it (and if yes, how), or do I have to write the script myself? Thanks. ...

SQL Server book recommendation

Hello Experts, I'm just a beginner in development field.Looking for SQL Server books. Please tell me which is good book to study from. Any suggestion and pointer will be really helpful . thanks ...

Which version of Ruby?

I'm just starting out in Ruby (Rails actually) and the book I'm reading covers Ruby 1.8.6, RubyGems 1.0.1, Rails 2.0.2 and SQLite 3.5.4, but the current stable releases of these are 1.9.1, 2.3.8, 1.3.7 and 3.7.0 respectively, should I still proceed with the book or find another? Also, I couldn't find a recent guide/tutorial to walk me t...

Optimizing a MySQL query (Update + subquery)

I want to change the values of column this in table_one where this = 1 and id value of that row exists anywhere in table_two's column other_id So I'm using this query: UPDATE table_one SET this='0' WHERE this='1' AND table_one.id IN (SELECT other_id FROM table_two); The query took several hours without yet finishing, so I was wonderi...

I've added a comment in mysql tables, but still not added to my Drupal forum

hi, I'm using a forum with several topics and node comments module to add comment nodes to the topics. I'm importing an external database and adding the relationships directly in mysql to the node_comments table. However before to see the comments of a quesition I need to add a random comment in drupal to this question. Which is proba...

Calling a PHP Function into sql query

I have a MySQL table called "invoice_prod" : id | qte | price | id_tva And another table called "tva" : id_tva | content Now, I want to find the total of an invoice, so I made this query and it works. $sql_mnt = mysql_query(" SELECT SUM( (qte * price) * ( 1 + (tva / 100))) AS total_un_prod FROM invoice_prod WHERE i...

Modify FindAll function to a DoesExist function in SQL Server

I have the following recursive function: ALTER FUNCTION [dbo].[ListAncestors] ( @Id int ) RETURNS TABLE As RETURN ( WITH cte As ( SELECT UserId, ManagerId, Forename, Surname FROM dbo.Users WHERE Use...

Date Range SQL Query

Schedule_ID---Card No----FromDate----ToDate 4-------------1000058----01-Aug-10---31-Aug-10 5-------------1000058----01-Sep-10---30-Sep-10 6-------------1000058----06-Oct-10---26-Oct-10 7-------------1000099----06-Oct-10---26-Oct-10 What will be the query so i can find that is 08-Oct-10 Exist for 1000058 in table SELECT Schedule_ID ...

SQL: list of points to rectangle

I have two tables: regions <id> points <region_id, x, y> Assuming there are exactly 4 points per region, and these points describe a rectangle -- is there an SQL query that will bring me this view: rectangular_regions <region_id, x1, y1, x2, y2> ? ...

Schema less SQL database table - practical compromise

This question is an attempt to find a practical solution for this question. I need a semi-schema less design for my SQL database. However, I can limit the flexibility to shoehorn it into the entire SQL paradigm. Moving to schema less databases might be an option in the future but right now, I' stuck with SQL. I have a table in a SQL d...