sql

MySQL - Reporting using a week to represent all rows with a datestamp in that week

I am trying to build a table of data where each row represents a week (i.e., 09/23-09/30). Each row is built from data from several different rows that have datestamps within that week period. The rest of the columns are subselects that typically give averages of all the data in all the rows in the week period. Logically, what is the be...

Dynamically creating and executing sql commands in oracle

I am taking a database class and at the beginning of the lab section of the class we usually have to drop all the tables in the database created previously. I wanted to be able to run a script that does this dynamically, but cannot seem to get it to work. Here is the code I have so far. declare tname string(50); cursor ctable is select ...

Tracking a user submitted rating in SQL

Consider a site where a user can rate games. The ratings available are: None, Liked, or Disliked. The code will keep track of the ratings just fine. The current problem is a user can rate a game and just keep re-rating it. How would a SQL table look if I wanted to store a user's rating for a certain game? Duplicate ratings for each user...

Do I have to use mysql_real_escape_string if I bind parameters?

I have the following code: function dbPublish($status) { global $dbcon, $dbtable; if(isset($_GET['itemId'])) { $sqlQuery = 'UPDATE ' . $dbtable . ' SET active = ? WHERE id = ?'; $stmt = $dbcon->prepare($sqlQuery); $stmt->bind_param('ii', $status, $_GET['itemId']); $stmt->execute(); $stmt->close(); } } Do I need to mysql...

Zend DB fetchAll(): where clause array with IN operator

I'm selecting records from a database using the equivalent of this query: SELECT * FROM reports WHERE user_id IN (3, 6, 22); The function calling fetchAll() has an argument that's an array of the user IDs, and this call works just fine: $resultSet = $this->getDbTable()->fetchAll('user_id IN (' . implode(', ', $userIds) . ')'); Howe...

put login and password in one table or in multiple tables for each type of user ?

I have different 3 types of users and each type of user can have columns and relationships with tables that another type doesn't, but all of them have login(Unique) and password, how would you do: create a table for each type or create one table for all of them or create a table for all of them only for login and password and separ...

Should a database table have default values?

I was having a discussion with a developer at work on the issue of should a table use default values. Is there a hard and fast rule on this or is it a gray area in best practices? ...

Is there a way to create an auto-incrementing Guid Primary Key in an Oracle database?

I mostly work with sql-server (when I do work with databases) and I am trying to learn pl-sql. Is there any equivalent to sql-server's auto-generated Guid as primary keys in Oracle? ...

Better data security with SQL on separate box from web server (Win 2003/SQL 2008)?

An auditor reviewing our system was suggesting that our data should be stored on a separate physical server from the web server. We're running SQL 2008 on a Windows 2003 machine with IIS as the web server running ASP.NET 3.5 applications. I can't think of any significant reason that there would more security by having SQL on a separate ...

How can I see the SQL that is created from a LINQ to SQL query?

How can I see the SQL that is created from a LINQ to SQL query? The obvious answer is to open up the SQL profiler and look at it there. Is there a way in VS? Maybe there is a VS add-on like a visualizer that allows you to hover over the DataContext to view the SQL. ...

Scala Actors suspends unexpected when connecting to a database

I have a problem with my understanding of the standard actor library in Scala. In the code below I have created a simple swing, which basically should test if it is able to connect to a postgreSQL server. However it doesnt make it that far, I use Actors since the UI otherwise would freeze up while doing the work needed to connect to the ...

File-based database asp.net

Is there some kind of simple database system that uses simple text or xml files for data storage? I just need some basic functionality like update,delete, insert, simple constraints and relations. For the project that I have now using SQL Server would be too heavyweight and I have never really liked it anyway. ...

When would you ever need to change a primary key's value?

Hello, I've been reading up on foreign keys and such for postgres and I noticed that it allows a cascading update for foreign keys. Well, my question is, when would you need to update the primary key of a row? Apparently this guy needs to http://www.oreillynet.com/onlamp/blog/2004/10/hey_sql_fans_check_out_foreign.html but I'm not qu...

Can primary index be a CHAR in MySQL?

My primary indexes are unique reference numbers like 002345 and 000023. If I format them as integers I loose my zero's. They need to be 6 digits. Can I use CHAR? I don't need any auto increments. ...

groupby msql query

Hi I have a bunch of ID's. I can assign them once a day. I need to query my database, see if they're any ID's available to hand out (if I didn't hand out all of them in a 24 hour period), and if there's any available, write a new row to the database saying that the ID has been handed out. I also need to know which user held which ID at...

How to iterate by row through a mysql query in php

Ok, So I am trying to query my database and select all rows that have a certain value. After that I turn the query into an array with mysql_fetch_array(), then I tried iterating by row through the fetched array using a for each loop. <?php $query = mysql_query("SELECT * FROM users WHERE pointsAvailable > 0 ORDER BY pointsAvailable Desc"...

How do I execute sql text passed as an sp parameter?

I have a stored procedure with an nvarchar parameter. I expect callers to supply the text for a sql command when using this SP. How do I execute the supplied sql command from within the SP? Is this even possible?- I thought it was possible using EXEC but the following: EXEC @script errors indicating it can't find a stored procedur...

SQL statement help!!

I need help in sql statement. Now my database I have Category table(book, flower, stationary). Inside book table(bookIDm,bookTypes, size, price, catID) For eg, 1st field -1(bookID), Diary(bookTypes), Big(size), $12.50(price), 1(catID) 2nd field -2(bookID), Diary(bookTypes), Big(size), $11.00(price), 1(catID) . Now I just wanted ...

How should I implement a one-to-one relationship in (My)SQL?

Hi, I'm relatively new to SQL and I'm not sure how to do a one to one relationship. Having read about the first 3 forms of normalization, I'm currently under the impression that the best way to do this would be to have an intermediary table, ala-3rd from many to many relationships, except each column being declared unique. ...

SQL: SUM and GROUP BY don't return any row on ASP

I have two tables as follow: t_product p_id, p_name... t_order o_id, o_product, o_quantity that is my query: SELECT t_product.*, t_order.* FROM t_product JOIN t_order ON p_id = o_product ORDER BY o_product it return: p_id | p_name | o_quantity --------------------------------- 01 | prod_01 | 30 01 | prod_...