mysql

Using auto_increments to join MyISAM tables in MySQL

Hi everyone While I've been writing php for a long time, it was always a skill I learnt myself and I've just hit a minor crisis of confidence over table joins! Assuming a MySQL db auth containing MyISAM tables users and permissions: users - id (auto increment) - email permissions - id (auto increment) - name To join these tables in...

Define Column Aliases?

I have a query like this: SELECT `*` FROM (`threads` t, `members` m) WHERE `m`.`id` = t.author AND `t`.`type` = '0' AND `t`.`category` = '1' And basically what happens is that there is an ID field in both tables (members and threads) so what's happening is that the results array is getting messed up. IE: There is only one...

Slashes in MySQL tables, but using PDO and parameterized queries. Whats up?

Alright, so my code to update my database tables is varying flavours of the following: $query = " insert into Comment (Comment, CommentDate, Rating, UserRid) values (:comment, now(), 0, :userrid )" ; try { $db_conn = new PDO('mysql:host='.$db_server.';dbname='.$db_name, $db_username, $db_password ); $db_c...

LNK2001 error when compiling windows forms application with VC++ 2008

I've been trying to write a small application which will work with mysql in C++. I am using MySQL server 5.1.41 and MySQL C++ connector 1.0.5. Everything compiles fine when i write console applications, but when i try to compile windows forms application exactly the same way (same libraries, same paths, same project properties) i get thi...

DB load CSV into multiple tables

UPDATE: added an example to clarify the format of the data. Considering a CSV with each line formatted like this: tbl1.col1,tbl1.col2,tbl1.col3,tbl1.col4,tbl1.col5,[tbl2.col1:tbl2.col2]+ where [tbl2.col1:tbl2.col2]+ means that there could be any number of these pairs repeated ex: tbl1.col1,tbl1.col2,tbl1.col3,tbl1.col4,tbl1.col5,tb...

How do you efficiently connect to mysql in php without reconnecting on every query.

I'm pretty sick of having to rewrite my code every time I learn something new about php (like the fact that mysql connections cannot be passed around in a session as a handle). How do you implement mysql connection in your projects? A lot of people have proposed "connection pooling", but after reading the manual i'm still lost. It's lik...

How do you use a variable in a $_POST[]

I need to iterate through a bunch of dynamically generated fields, but this doesn't work: $population_density = $_POST['$current_location_id']; I have a list of locations with their populations on one page; I need to make it so you can update a lot of them at once. So I made the field names dynamically correspond to the location_id. W...

Join two rows Distinctly

If i have a table that looks like num 1 2 3 4 5 6 7 8 9 And i want to display the same table in two columns SELECT t1.num, t2.num FROM (SELECT * FROM x) AS t1, (SELECT * FROM x) AS t2 So the result set looks like num 1,1 2,2 3,3 4,4 5,5 6,6 7,7 8,8 9,9 How would i go about doing this in MySQL EDIT Sorry i didnt want to make th...

Where Clause Woes in Stored MySQL Query

What I'd like to do is execute a MySQL query containing a where clause ("result query") that is stored in a column in the database. This column, containing the query, is a result of another query ("original query"). The catches: The result query's where clause can contain a variable value (or two) I don't know what the result query ...

mysql: Using LIMIT within GROUP BY to get N results per group?

The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2009 p01 4.4 2002 p01 3.9 2004 p01 3.5 2005 p01 2.1 2000 p...

How to verify if two tables have exactly the same data?

Basically we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema. At the beginning both tables (original table and backup table) contains exactly the same set of data. After sometime for some reason I need to verify whether dataset in the original table...

MySQL Basic about relationship

Hi all! My doubt is about how to treat the follow thing: I have a system where a user belong to a company, and this user have their clients. How is the right way to get a list of all company clients and the follow user name?? In the client table where i have a field with the one of this relations: A company_id and user_id field Ju...

How do I find out the index in a MySQL table

Hey guys, I'm programming in PHP5 & MySQL5 and I'd like to know how to figure out the index of an entry. To be clear, here's an example: I have 500 users in a table, I want to select the user johndoe and the index of that user, so if johndoe is the 100th user in my table, I want to be able to select the information for him in a similar w...

mysqld-nt.exe exist in task list,but actually it's not running?

Have you ever step into this kind of problem? I tried: telnet localhost 3306 And it fails to connect. I can see mysqld-nt.exe in the task manager(I'm using windows platform). So I restarted the server,and it's ok. This happens every day. Any ideas? EDIT Here is the error log(I didn't find anything abnormal though): 100122 10...

Connecting to MySQL via SSH with PHP

Is it possible to connect to a MySQL database, on remote server, via SSH using PHP that is installed on my local computer? I am a bit confused on this subject. My shared hosting provider suggests I use programs like MySQL Query Browser and Microsoft SQL Server Management Studio Express. I have done some research on SSH at php.net (as ...

Java: Rollback Database updates?

I have three methods that, together, save a node to the database. Currently, this does not achieve the desired effect of not committing the changes unless all the writes are successful. conn.setAutoCommit(false); writeNodeTable(node, newNodeNID); writeContentTypeBoutTable(node, newNodeNID); writeTerms(node, newNodeNID); ...

(SQL) How to set column names of MySQL table as the delimited first line of my infile?

The first line of my infile is actually the name of the columns delimited in the file. Is there anyway for a mysql import to use those column names in the file, and create columns in the mysql table with those names so that I don't have to? ...

MYSQL - Cannot able to drop a column.

Hi, I am using MYSQL 5.1. When i try to drop a column in a table, it throws the following error. MATERIAL_OUTWARD_ID is a foreign key. Query: alter table `tispa`.`customer_invoice` drop `MATERIAL_OUTWARD_ID` Error: Error on rename of '.\tispa\#sql-78_8' to '.\tispa\customer_invoice' (errno: 150) ...

mySQL return not empty field

Hello, I have two fields, but I want to return only the non-empty one. select firstname, lastname, (if firstname != empty, then firstname else lastname) as name from users; How do I go about doing something like above. Am not sure what should be inside the round brackets. I don't want to use PHP post-processing. ...

Dropping a column from a "$result" resource after a db query

After a simple query that is successful, $sql = "SELECT * FROM mytable" ; $result = mysql_query ( $sql ) ; is there a way to "drop" a specified column from the $result resource? IOW, the $result consisted previously of 8 fields, but afterwards contains of only 7. Thanks. ...