mysql

inner join... how to get all fields from one table ?

Hi, I want to get all fields from one table and use DISTINCT with the second table. I have this: SELECT stats.*, DISTINCT(visit_log.blog_id) AS bid FROM stats INNER JOIN visit_log ON stats.blog_id = visit_log.blog_id But I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL se...

edit query error with foreign key constraints

ok here it is. i have a table product with foreign keys from product_sizes,brands,categories, and suppliers table. when i display the products table to a html. i use JOIN so that the data displayed in the products table is not the id's of the foreign keys but rather its corresponding names.. i have no problem with the query in regards of...

How to change 08:00:00 to 08:00 in PHP/MySql

I have time type in mysql and I input time with format of HH:MM. This is stored in DB as HH:MM:00. Now when I display, it shows seconds as well. I only want to show hour and minutes. In stead of 13:20:00, showing 13:20 etc. What is the best way to do it. I can use substr($time, 0, 5), but I am wondering if there is a better way to d...

MySQL multiple tables problem?

I want to be able to check my email if it equals $e but my email is in another table called info how can I can I fix this problem? Here is my MySQL query so far. "SELECT password, salt FROM users WHERE (email = '" . $e . "' OR username = '" . $e . "') AND active IS NULL" ...

MySQL Update problem.

My mysql query is not updating my database correctly can some one help me fix this problem I think it has something to do with the JOIN. Here is my mysql query. "UPDATE users SET users.last_login = NOW() FROM users JOIN info ON info.user_id = users.user_id WHERE (info.email = '" . $e . "' OR users.username = '" . $e . "') AND u...

Efficient way to manage the relations between two database tables on the front-end using checkboxes ?

Quick background info: of the three database tables that are relevant for this question, one holds devices info (d.id, d.name), the second one holds accessories for the devices in the first table (a.id, a.name) and the third table handles relations between the first two (d.id, a.id). By selecting one of the available devices from a drop...

Not working mysql_query in php (quotation marks probs)

$ikona = "layout/achiv/a_icon.png"; //$opis = string of text without quotation marks $addit = '<img src="'.$ikona.'" onclick="alert(/'On day '.date("Y-m-d H:i:s").' user has '.htmlspecialchars($opis).'/'); ">'; mysql_query("UPDATE `accounts` SET `this_damn_cell`='".$addit."' WHERE id='".$_POST["id"]."' ") or die(mysql_error()); //error...

Select available rooms between two dates

I have above mysql table with available dates and prices. Second table includes room details. How can I join two tables to get available rooms between two dates and not get duplicate content. ...

Database performance with looped update queries?

I am currently structuring my queries for a database update model with CodeIgniter. I getting the form posted input keys with $keys = array_keys($_POST) To update according database fields I was wanting to do something like this foreach($keys as $key){ $data = $this->input->post($key); $this->db->query("Update $table SET '$key...

MySQL Query Help

I have the following table structures: matches: +-------------------+---------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------------+------+-----+-...

MySQL delete and update multiple rows

Say I have an entity A in one table and an arbitrary number of other entities B related to it in other table. I have a situation where I need to replace all those B entities with a list of new B entities in a way that there should be no duplicates. Simple way would be to issue a DELETE query first to remove all the old ones and then a...

are we doomed yet?

Hi, so my boss asked me to import a joomla database of about 250 users into a Prestashop database (of users). After some analysis, i've found out that prestashop and joomla use different password salting techniques. So my qestion is: "Are we doomed yet?" ...

SQL JOIN two tables to access all of the data

I am needing to connect two tables by id then access any of the data from either of them inside a foreach loop. I've never done any LEFT JOINS or anything so I'm not sure how to start. The first table named "sys_acl_levels_members" looks like this: IDMember IDLevel DateStarts DateExpires TransactionID The other table "Profiles" has...

Two Questions - Web Programming

1) I know HTML/CSS. Where do I go from here to learn how to build web sites and eventually web apps? I was thinking Javascript (jQuery, yeah?) and PHP/MySQL. Is that on the right track? 2) I know java. How hard will it be to learn what you recommend in question 1. And where/how do you recommend I learn? ...

PHP: Instead of defining onlinestatus, show only those who are online.

Yes, so I have this: $query = "SELECT *, CASE WHEN last_access < UNIX_TIMESTAMP()-150 THEN 'offline' WHEN last_access < UNIX_TIMESTAMP()-80 THEN 'idle' ELSE 'online' END AS online_status FROM users"; That'll do if you call online_status, it will either give you offline, i...

Using mySQL, how do I order by date across several different tables?

I'm attempting to generate a dynamic RSS feed for a website, and I'm running into trouble writing a mySQL query for it. I have several seperate tables containing data I need to access, and I want to sort across all of them by date. As a sample table: Chapters release ChapterTitle ChapterContent ExtraInfo 10/12 'Chapter 1' 'Bla bla b...

Am I joining these 4 tables correctly?

My query is terribly big: SELECT n1.*, n2.rating, n3.*, n4.* FROM stats n1 INNER JOIN members n2 ON n1.n_id = n2.id INNER JOIN add_info n3 ON n1.n_id = n3.n_id INNER JOIN other_table n4 ON n1.code = n4.code WHERE n1.n_id = {$member_id} AND n1.code='{$some_code}' Am I doing this correctly? If yes, can I make it better, more optimal? Be...

Inaccessible mysql table inside subquery

Hello SELECT ka.id, ka.user FROM {auctions} ka WHERE (SELECT MAX(inst) FROM (SELECT SUM(installment) AS inst FROM {payback} kp WHERE id IN (SELECT id FROM {auctions} WHERE user = ka.user) GROUP BY scheduleD...

DB pull from heroku fails with Encoding::CompatibilityError

I'm having some problems performing a db:pull from heroku back to my local development enviroment. My setup is MySQL (5.1.50) from a MacPorts install on a local Rails 3/Ruby 1.9.2 environment under Mac OS X Snow Leopard via RVM, and I've successfully performed a heroku db:push to the production site on Heroku many times. However, on a s...

Ruby on Rails: uninitialized constant MysqlCompat::MysqlRes

I tried all the options specified in the link: http://stackoverflow.com/questions/1332207/uninitialized-constant-mysqlcompatmysqlres-using-mms2r-gem but nothing is working. I have an iMac with Snow Leopard installed. I am currently using the mysql gem version 2.7 and I checked that ruby was 64 bit but I am unsure if the mysql gem is 64...