Php mysql joins


hi, am looking for some guidance with my SQL...

OK so I am trying to grab all topics from TOPIC which have a certain parent id (WHERE TOPIC.pID = 3 or whatever) but also match each returned row to the USER table to get the users name for the last post in that topic (match TOPIC.lastUID with USER.ID)

problem is its only returning one row

Code: "SELECT TOPIC.ID, TOPIC.pID, TOPIC.lastUID, TOPIC.TITLE FROM TOPIC INNER JOIN USER ON TOPIC.lastUID = USER.ID WHERE CP.pID = '$FORUM_id'";

can I do this all in one sql statement

Posted On: Monday 29th of October 2012 05:56:14 AM Total Views:  358
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Trigger mysql 5.0 insert 2 rows as one row

I have trouble in trigger mysql5.0 and need help now I want to insert 2 rows from deposit into T so in tabel T it would became 1 row in one id on text field from tabel deposit, I work on phpmyadmin, it return an error like the following: Code: INSERT INTO T( tel_number, text ) VALUE( '09999', SELECT ( SELECT text FROM deposit ORDER BY id DESC LIMIT 1 , 1) AS a, ( SELECT text FROM deposit ORDER BY id DESC LIMIT 2 , 1) AS b) MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT (
VIEWS ON THIS POST

166

Posted on:

Monday 29th October 2012
View Replies!

Range data - mysql query

I have two tables in my database. Code: Table Name: product_price_range Field Type ----------------------------- price_category_id int(11) title varchar(155) min_price float max_price float Data: product_price_range price_category_id title min_price max_price ----------------- ----------- --------- --------- 1 1-49 1 49 2 50-99 50 99 3 100-199 100 199 4 200-299 200 299 5 300-erboven 300 0 Second Table: product Field Type ----------------------------- product_id int(11) title varchar(155) price float Data: product product_id title price ---------- ----------- --------- 1 product1 49 2 product2 99 3 product3 149 4 product4 249 5 product5 300 Now I want to show the only those price range in which product price is available. For that I need a query or other possible way to identify those price range. In above example output should be 1. 1-49 2. 50-99 4. 200-299 Can anyone help me in this issue
VIEWS ON THIS POST

122

Posted on:

Monday 29th October 2012
View Replies!

Problem with batch file to dump data into mysql

I am having problems with a batch file which I have written to dump the contents of a file into a mysql database. This batch file was working earlier before I accidentally deleted the root account in phpmyadmin. So I am using a different account now which has full priveleges. Any below I have pasted in the batch file, please let me know if anyone can see the issue. @echo off echo Connecting to Database....... cd\ F: cd F:\wamp\bin\mysql\mysql5.1.36\bin mysql -u -p " use ws4; load data infile 'DUMPFILE.txt' into table tablews6"; When I run the batch file it says that the system cannot find the file specified. Im not sure why not. I can run the exact commands from the command line manually and it work, although it does say I have an error in my sql, but it does work. This worked before with the root account without a password before I deleted it. Please let me know if anyone can see what the problem is. Many
VIEWS ON THIS POST

121

Posted on:

Monday 29th October 2012
View Replies!

Import select tables from mysqldump of entire database

I have a database backup that I created with mysqldump. I need to import a few tables from this backup, but NOT the entire sql file. I would normally do something like this: Code: mysql databasename < /home/.../filename.sql -u user --password=password But what if I only want table1 and table2 from filename.sql Is this possible
VIEWS ON THIS POST

226

Posted on:

Monday 29th October 2012
View Replies!

Inserting future dates into mysql database

I have a booking system and need to provide my user with a bunch of future dates (to choose as a delivery date) - is there an easy way to do this The problem I see is that I write sql to insert the next day 7 days into the database, but then I don't wish to replicate this in the database the following day. Has anyone got any suggestions Cheers, Garry. , I basically have 2 drop down forms, one is populated with a list of dates (eg Mon 22nd June 2009) and the other is a list of hours belonging to that date (eg 8:00am, 9:00am, 10:00am and so on..) I have 2 tables in my database... 'day' and 'hour' which are linked with a 'day_id' field so the day is conneted with specific hours via an ID. Is this not the best way to go about showing available dates The dates obviously have to be stored somewhere as once someone books a time, that time will need to be excluded from other users trying to book it.
VIEWS ON THIS POST

121

Posted on:

Monday 29th October 2012
View Replies!

Error when pasting long query into mysql terminal

I have a very long load data infile statement that I cannot paste into a mysql terminal window (about 78 lines). Well, I can paste it, but it does very strange things...the information is pasted out of order and duplicated in some places and when I run the statement it creates all of the rows, then deletes them, leaving 1 row that is not formatted properly. Oddly enough, I have another statement that is almost the same length and I don't have a problem with that one. Both were created in flat notepad documents. I have the same problem even if I execute the statement from an external text file. I get the same results as when I try to run it directly in the terminal. Code: mysql database < /location/of/statement.txt Is there a limit to the number of lines a statement can have in a terminal window OK, so I just tried the same query on another server and it works in that terminal window Is there a clip board or cache that needs to be cleared from mysql every once in a while Why would it work on one and not on another. I tried to restart mysql on my local machine (the linux server that is not working properly) and it did not solve the problem. Hmmm.
VIEWS ON THIS POST

129

Posted on:

Monday 29th October 2012
View Replies!

Migrating mssql to mysql problem

I am migrating a mssql database to a mysql with 'MySql Migration tool'. The mssql database has tables with colums of datatype 'text' which will be migrated as longtext. I have googled and I have tryed different character set and collations but I havn't succeeded to migrate the swedish letters , , and others for these columns. Example: 23_ndringar => 23_..ndringar (32 33 5F C3 84 6E 64 72 69 6E 67 61 72) ndringar => ...ndringar (EF BF BD 6E 64 72 69 6E 67 61 72) tillgg => till...gg (74 69 6C 6C EF BF BD 67 67) (E5) => most EF BF BD but sometimes C3 A5 (E4) => most EF BF BD but sometimes C3 A4 (F6) => most EF BF BD but sometimes C3 B6 (C5) => EF BF BD (C4) => EF BF BD (D6) => EF BF BD (E9) => EF BF BD (C9) => EF BF BD _(5F C5) => 5F EF BF BD _(5F C4) => most 5F EF BF BD or sometimes 5F C3 84 _(5F D6) => 5F EF BF BD Can any of you give me an idea how to solve this problem Best regards
VIEWS ON THIS POST

127

Posted on:

Monday 29th October 2012
View Replies!

Corrupt InnoDB table not letting mysql start!

. Somebody tripped over my server's power cable and hard shut it down. Unfortunately, it must have been in the middle of a write operation or something, but MySQL no longer starts up. I really need to recover mysql and make it be operational again. Any suggestions how I can get back online again I've tried setting innodb_force_recovery = 1, but that hasn't made any difference. Any help would be greatly appreciated!! Thank you so very much! Code: -n 082019 09:20:58 Starting mysqld daemon with databases from /var/mysql -n 082019 09:20:58 mysqld started 081219 9:20:58 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 081219 9:20:58 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 2 1870325554. InnoDB: Doing recovery: scanned up to log sequence number 2 1870325554 InnoDB: Page directory corruption: supremum not pointed to 081219 9:20:58 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 0000000000000[TRUNCATED MANY MORE 0s]; asc [TRUNCATED MANY SPACES] ;InnoDB: End of page dump 081219 9:20:58 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 0, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0 InnoDB: Page directory corruption: supremum not pointed to 081219 9:20:58 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 00000000000000[TRUNCATED MANY MORE 0s]; asc [TRUNCATED MANY SPACES] ;InnoDB: End of page dump 081219 9:20:58 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 0, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0 081219 9:20:58InnoDB: Error: trying to access a stray pointer 88b1fff8 InnoDB: buf pool start is at 8b10000, end at 9b10000 InnoDB: Probable reason is database corruption or memory InnoDB: corruption. If this happens in an InnoDB database recovery, see InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: how to force recovery. 081219 9:20:58InnoDB: Assertion failure in thread 1 in file ./../include/buf0buf.ic line 259 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. 081219 9:20:58 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=258048 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 31599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -n 082019 09:20:58 STOPPING server from pid file /var/mysql/mysql.pid -n 082019 09:20:58 mysqld ended
VIEWS ON THIS POST

152

Posted on:

Monday 29th October 2012
View Replies!

Putting 900charactrs in a mysql cell help

, I have a database that will hold answers to specific class questions however some of the answers are a bit lone 900 or so characters. I am wondering if its possible to store that many characters in a mysql cell and if so what type shold the cell be I tried longtext and also longblob infact I tried just about every type and still does not work \t Help
VIEWS ON THIS POST

141

Posted on:

Monday 29th October 2012
View Replies!

Mysql pegging one proc to 100% why? How to stop unwanted mysql procs

When I run top I have mysql_safe running its *** off and it is pegging one cpu core to almost 100% but the other core is sitting at 0. What is this mysql_safe I also have a mysqld process that is doing a little at the same time but nothing like mysql_safe. I think the mysqld process may be the actual process I need. And why isn't the mysql_safe process spreading across cpu cores When I do to /etc/init.d I find the 3 mysql's... mysql mysql ndb mysqlndb-mgm I did some searching and saw that mysql ndb is for clustering. I am not doing any clustering, this DB is about 1 GB and nothing extremely huge. Can someone explain what I can get rid of/ how I can find out what apache/php is using, how I can stop ones I don't need I did install mysql with sudo apt-get install mysql.x.x Thank you Cheers, gbrent
VIEWS ON THIS POST

166

Posted on:

Monday 29th October 2012
View Replies!

Synchronize offline with online mysql

Hi I have project where we have a main site with mysql/ php and then we will have laptops at different sites or shops that will capture data from customers and some of these will go offline sometime. Now my question is what is the best platform to use to capture data offline and then synchronize it with the main server when it goes online again. Someone said to me Adobe Air and then there is also flash but what do you think is the best to use for something like this L.
VIEWS ON THIS POST

224

Posted on:

Monday 29th October 2012
View Replies!

Updating text in mysql

Hi I apologise if this has already been answered elsewhere but i wasn't sure what to search for. Someone has deleted a folder structure in our cms which was simply a demo of the site they created. files still exist but no longer in the demo directory. However within the html in the database there are LOADS of links still referencing the demo directory and therefore the site is not pretty much ruined as the links no longer work. (bunch of cowboys who made the site) What i want to know is: Is there a way in MySql that I can simply update the links in the html from /demo/ to simply / without obviously deleting all the existing html. Obviously a standard update using '%/demo/%' to '/' would overwrite all the other html. Could anyone tell me how this can be achieved using the update function rather than me going into each and every single link and editing manually.
VIEWS ON THIS POST

120

Posted on:

Monday 29th October 2012
View Replies!

Creating a timestamp in a mysql_query

I have a table which I made a long time ago that has month, day, and year fields but no timestamp field. Is there a way to run a mysql_query to get a unix timestamp by using the month, day, and year fields
VIEWS ON THIS POST

133

Posted on:

Monday 29th October 2012
View Replies!

Issue running mysql_fix_privilege_tables.sql

I upgraded my SQL server from version 4 to 5.0.58. all is running fine but I get a warning when wanting to set any permissions that I should run the mysql_fix_privilege_tables.sql command. Upon doing so I get the following errors: Quote: ERROR 1060 (42S21) at line 75: Duplicate column name 'File_priv' @hadGrantPriv:=1 1 1 ERROR 1060 (42S21) at line 81: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 82: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 83: Duplicate column name 'Grant_priv' ERROR 1060 (42S21) at line 94: Duplicate column name 'ssl_type' ERROR 1061 (42000) at line 105: Duplicate key name 'Grantor' ERROR 1054 (42S22) at line 131: Unknown column 'Type' in 'columns_priv' ERROR 1060 (42S21) at line 153: Duplicate column name 'type' @hadShowDbPriv:=1 1 1 1 ERROR 1060 (42S21) at line 163: Duplicate column name 'Show_db_priv' ERROR 1060 (42S21) at line 180: Duplicate column name 'max_questions' ERROR 1060 (42S21) at line 190: Duplicate column name 'Create_tmp_table_priv' ERROR 1060 (42S21) at line 193: Duplicate column name 'Create_tmp_table_priv' ERROR 1054 (42S22) at line 282: Unknown column 'Create_view_priv' in 'where clause' ERROR 1054 (42S22) at line 317: Unknown column 'Create_routine_priv' in 'where clause' ERROR 1054 (42S22) at line 366: Unknown column 'Create_user_priv' in 'where clause' ERROR 1060 (42S21) at line 386: Duplicate column name 'Routine_type' -------- Afterwards things do seem to be running fine though, no issues so far but I'm nevertheless worried that I should be concerned about the above errors. Any advice would be appreciated.
VIEWS ON THIS POST

133

Posted on:

Monday 29th October 2012
View Replies!

Extract part of a date mysql colum type

I have a blog system I am building. One of the parts of the system will be where a visitor can click a link that is the name of a month (i.e., October) and be able to see only blog posts that where posted in the month of october (regardless of year) So my issue is I have a mysql that has a colum named Blogdate that is of type date now I am trying to get this mysql to work (although I know it is wrong) I am not sure how this can be done. I basically have to use only a partion of the variable that is in the Blogdate colum to search by here is my sql Code: $themonth = $_GET['month']; $sql = mysql_query("SELECT * FROM Blog EXTRACT(MONTH FROM '$Blogdate') as themonth WHERE themonth=='$themonth'"); while($row = mysql_fetch_array($sql)) { extract($row); echo "$blogtitle posted by $postername"; } ,
VIEWS ON THIS POST

134

Posted on:

Monday 29th October 2012
View Replies!

Possible corruption of mysql schema?

all. I suspect that I have corrupted my default schema for the 'mysql' database - where the 'user' table is stored. MySQL experience: Little - but not complete newbie. Premise: I cannot login to my mysql instance with what I think is the correct root password. So, I tried to reset my root password. To do this, my steps are: 1) Using MySQL System Tray Monitor, turn off grant tables. 2) Restart MySQL. 3) Login to mysql using: mysql -u root -p mysql 4) Do: UPDATE user SET host='localhost' and password=PASSWORD('password') where user='root' 5) Error/warning: Error 1292 (22007): Truncated incorrect DOUBLE value: 'localhost' I take this to mean that my mysql schema is corrupted
VIEWS ON THIS POST

156

Posted on:

Monday 29th October 2012
View Replies!

Sql developer and the com.mysql.jdbc.driver

i downloaded and installed "mysql-connector-java-5.0.8" and pointed my environment variable to "mysql-connector-java-5.0.8-bin.jar" and i get a unable to find driver: com.mysql.jdbc.driver what should i install exactly , well i found some info in the installer. but i dont understand it Quote: 1.2.2. Installing the Driver and Configuring the CLASSPATH Once you have extracted the distribution archive, you can install the driver by placing mysql-connector-java-[version]-bin.jar in your classpath, either by adding the full path to it to your CLASSPATH environment variable, or by directly specifying it with the command line switch -cp when starting your JVM. If you are going to use the driver with the JDBC DriverManager, you would use com.mysql.jdbc.Driver as the class that implements java.sql.Driver. You can set the CLASSPATH environment variable under UNIX, Linux or Mac OS X either locally for a user within their .profile, .login or other login file. You can also set it globally by editing the global /etc/profile file. For example, under a C shell (csh, tcsh) you would add the Connector/J driver to your CLASSPATH using the following: shell> setenv CLASSPATH /path/mysql-connector-java-[ver]-bin.jar:$CLAS SPATH Or with a Bourne-compatible shell (sh, ksh, bash): export set CLASSPATH=/path/mysql-connector-java-[ver]-bin.jar:$CLASSPA TH this part talks about the driver its looking for. but i have no idea what its telling me to do really Quote: If you are going to use the driver with the JDBC DriverManager, you would use com.mysql.jdbc.Driver as the class that implements java.sql.Driver. this is on Vista. the mysql server: Server version: 5.0.45-community-nt and im using oracle mysql developer. im trying to add a mysql db in the mysql tab. and im getting this error.
VIEWS ON THIS POST

136

Posted on:

Monday 29th October 2012
View Replies!

UNION from mysql 5 to mysql 4

i know this is a bit backwards, but it had to be done due to our hosting company, we used to be hosted on MySQL 5 but we recently upgraded and for some really strange reason we have gone back down to MySQL 4. anyway to the problem, i have a UNION query which worked fine in MySQL 5 but wont work in MySQL 4, any advice or help would be much appreciated! Code: select * from crime_blocks, crime_gangs, (select position_number as gn from crime_positions where account_number = '$accountnumber' and game_number = '$city' and position_type = '1' UNION DISTINCT select cf_gang_number as gn from crime_ crime_positions where position_number = cf_ally_gang_number and account_number = '$accountnumber' and friend_game_number = '$city' and game_number = '$city' and cf_agreed = '1' UNION DISTINCT select cf_ally_gang_number as gn from crime_ crime_positions where position_number = cf_gang_number and account_number = '$accountnumber' and friend_game_number = '$city' and game_number = '$city' and cf_agreed = '1' ) as all_gangs where block_game_number = '$city' and all_gangs.gn = block_position_number and all_gangs.gn = gang_position_number and gang_game_number = '$city' and block_position_number = block_owner the union is just breaking the whole page now, and im getting the error, "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select position_number as gn from crime_positions"
VIEWS ON THIS POST

112

Posted on:

Monday 29th October 2012
View Replies!

Replace queried mysql results with other values

This query will replace values in a single column with pre-defined values: PHP Code: selectUser_ID,casewhenAnswer_Number_1=1then'cat'whenAnswer_Number_1=2then'dog'whenAnswer_Number_1=3then'fish'endasanswer fromtablenamewhereUser_ID='whatever'; That works great for 1 column, Answer_Number_1. But, if I have 25 Answer_Number_# columns, it doesn't work so well. Every value for an Answer_Number column will be a 1,2,or 3 so I need something that will account for all the columns and output cat, dog, or fish instead of 1,2, or 3. Any suggestions would be greatly appreciated.
VIEWS ON THIS POST

138

Posted on:

Monday 29th October 2012
View Replies!

Can one mysql table handle thousands of rows?

I am writing a diary program using php and mysql. Right now I have multiple tables each designed to store diary entries for one year (d2008 for year 2008, d2009 for year 2009 etc). I am wondering if this is a good way or I should just use one table to store all diary entries The table structure is very simple. It contains only 6 fields: id dtime day weather topic content The program does some searches like displaying few previous days' diaries on the first page, and a content search function. It's not very complicated. Currently the program is aimed to contain five year diaries but I might expand it to more than five. So can one table handle this or my current structure is better Thank you.
VIEWS ON THIS POST

121

Posted on:

Monday 29th October 2012
View Replies!