Jump to content

GregL83

Members
 View Profile  See their activity

  • Posts

    119

  • Joined

  • Last visited

    Never

 Content Type 

Posts posted by GregL83

    Left Join pass variable inside parentheses query???

    in MySQL Help

    Posted

    Hey all,

     

    I'm trying to write a query that invovles 3 tables.  The tables are: users, user_pictures, and pictures.  Basically, each user can have multiple user_pictures, linked by user_id, which relate to a picture in the picture table with a unique picture_id.

     

    users cols: id, name

    user_pictures cols: user_id, picture_id

    pictures cols: id, url

     

    I need to return user_id and picture url.

     

    here is the query I have thus far...  I was trying to use a variable inside the parenthese query to say only look for pictures that are relevant to the current user from the outer select statement...  Is there a way to pass that variable to the inner select statement??? or just a better way to write this query???

     

    $query = "SELECT u.id AS uid, u.username, u.sex, u.birth_date, p.url AS picture, u.register_date FROM users u LEFT JOIN (SELECT us.user_id, us.picture_id, up.url FROM user_pictures us LEFT JOIN pictures up ON us.picture_id = up.id WHERE us.user_id = uid AND up.status = 'active' ORDER BY us.picture_id ASC LIMIT 1) p ON u.id = p.user_id ORDER BY u.id DESC LIMIT 5";

    Return result set even when a join is null

    in MySQL Help

    Posted

    Hello,

     

    I am trying to return a mysql result with multiple table joins...  I need to return these results even if one of the table join returns null...  Is this possible???

     

    here is my query:

    SELECT sc.name AS name, sc.slug AS slug, ct.name AS city, st.name AS state, sp.picture_id FROM scenes sc JOIN cities ct ON sc.city_id = ct.city_id JOIN states st ON sc.state_id = st.state_id JOIN (SELECT scene_id, picture_id FROM scene_pictures ORDER BY picture_id DESC LIMIT 1) sp ON sc.id = sp.scene_id ORDER BY sc.id DESC LIMIT 5

     

    the table scene_pictures sometimes DOES NOT have any pictures, but I would still like to return the rest of the fields...

     

    Any help???

     

    Thanks in advance

     

    Data from more than 2 tables after one to many table join???

    in MySQL Help

    Posted

    I tried this and it return zero results:

     

    $query = "SELECT s.id, s.name, s.slug, ct.name, s.overall_comments, uc1.scene_id, uc1.comment, uc1.update_time FROM scenes s, cities ct, user_comments uc1 JOIN(SELECT scene_id, MAX(update_time) AS time FROM user_comments GROUP BY scene_id) AS uc2 ON uc1.scene_id = uc2.scene_id AND uc1.update_time = uc2.time WHERE ".likeQuery($keywords)." AND uc1.scene_id = s.id AND s.city_id = ct.city_id"

     

    Even if I just add an additional table to the FROM sequence, mysql stops returning results... any ideas???

    Data from more than 2 tables after one to many table join???

    in MySQL Help

    Posted

    Hello,

     

    I have a query that joins two tables:

     

    $query = "SELECT s.id, s.name, s.slug, s.overall_comments, uc1.scene_id, uc1.comment, uc1.update_time FROM scenes s, user_comments uc1 JOIN(SELECT scene_id, MAX(update_time) AS time FROM user_comments GROUP BY scene_id) AS uc2 ON uc1.scene_id = uc2.scene_id AND uc1.update_time = uc2.time WHERE ".likeQuery($keywords)." AND uc1.scene_id = s.id"

     

    likeQuery is a function that returns a LIKE string for searching the particular fields... this all works fine

     

    I would like to add additional tables to the mix...  Any idea how to do this?

    Return row's with max (lastest) update time for a product

    in MySQL Help

    Posted

    Hey,

     

    I am trying to return the latest comments for a product.  Each product could have multiple comments (rows), but I only want to return the latest comment (based off timestamp).  Here is an example:

     

    • prod_id  | comment  | time
    • -------------|--------------|--------------
    • 1            | a              | 1270018856
    • 1            | b              | 1270018801
    • 2            | c              | 1270018870

     

    should return:

    • prod_id  | comment  | time
    • -------------|--------------|--------------
    • 1            | a              | 1270018856
    • 2            | c              | 1270018870

     

    any help???? thanks :)

    Help with LIKE search and Many to One relationship... :( GURUs???

    in MySQL Help

    Posted

    Just an update... I've been searching long and hard to find a solution to this problem.  I fear there is a simple solution i've overlooked.  The closest i've gotten I can limit the results to 1 record per 'scene' by using GROUP BY.  I can also retrieve the max update time using MAX(update_time); however, the comment isn't retrieved from the row with the max update time...

     

    $query = "SELECT s.id, s.name, s.slug, s.overall_comments, uc.comment, MAX(uc.update_time) FROM scenes s, user_comments uc WHERE (name LIKE '%".$phrase."%' OR slug LIKE '%".$phrase."%') AND uc.scene_id = s.id GROUP BY s.id";

     

    Anyone???

    Help with LIKE search and Many to One relationship... :( GURUs???

    in MySQL Help

    Posted 

        $query = "SELECT DISTINCT s.id, s.name, s.slug, s.overall_comments, uc.comment FROM scenes s, user_comments uc WHERE (name LIKE '%".$phrase."%' OR slug LIKE '%".$phrase."%') AND uc.scene_id = s.id ORDER BY uc.update_time DESC";
    $result = mysql_query($query);

     

    here is the two arrays outputted... they are the same record, but there are 2 associated comments....

     

    Array ( [0] => 1 [id] => 1 [1] => the pour house bar & grill [name] => the pour house bar & grill [2] => the-pour-house-bar-grill-boston-massachusetts [slug] => the-pour-house-bar-grill-boston-massachusetts [3] => [overall_comments] => [4] => blah! [comment] => blah! ) Array ( [0] => 1 [id] => 1 [1] => the pour house bar & grill [name] => the pour house bar & grill [2] => the-pour-house-bar-grill-boston-massachusetts [slug] => the-pour-house-bar-grill-boston-massachusetts [3] => [overall_comments] => [4] => [comment] => )

    Help with LIKE search and Many to One relationship... :( GURUs???

    in MySQL Help

    Posted

    Hello,

     

    I'll create a case study for this to make sense...

     

    I have two tables T1 and T2.

     

    T1 has a list of books.  T2 has pictures associated with the books in T1.  More than one picture can exist for a book in T1.  So, the books have a 1 to many relationship with the pictures.

     

    I created a search to return books from T1... And it works fine.  I then wanted to return a picture with each book from T2.  The problem is that T2 may have more than one image associate with a book in T1.  This results in the book being returned as a result more than once...  I have been search for logic to only return the latest (i have timestamps) picture from T2 for each book in T1 so that I don't return duplicate results of books.

     

    So far my query is as follows:

    YourScene.us site critique

    in Website Critique

    Posted

    Thanks for your feedback thewooletmammoth, the coloring in the original designs were infact a black background.  Other darker backgrounds were also tested.  After alot of thought and comparison with competitors it was decided to use a light background.  While a dark background might represent better the mindset of a user at YourScene.us, it makes for a harder read and for various other reasons often doesn't translate well to general users.  The decision to go light was then made.

     

    Has anyone else had a chance to review the site... particularily function and/or usability???

    Imagemagick vs GD

    in Applications

    Posted

    Hey all,

     

    For the purpose of my site, i needed to use imagemagick.  What is really weird, is the test I have done in GD the images are far smaller in image size.  To get near the same image size in imagemagick i reduce the quality but there is no comparison in the outputed image.  Is there something I'm missing???

     

    Why does GD seem to render jpeg's at a smaller size and better quality???

    MySql Transaction Rollback with multiple queries

    in MySQL Help

    Posted

    I am trying to rollback more than one insert statment inside a transaction.  I thought everything was working fine; however, i'm not sure if its my new hosting.  Only the first query of the set seem to rollback.

     

    Here is an example:

    mysql_query("BEGIN");

$result = array();
$result = mysql_query($insert_1);
$result = mysql_query($insert_2);
$result = mysql_query($insert_3);
$result = mysql_query($insert_4);

if(!in_array(null, $result)){
  mysql_query("COMMIT");
}
else{
  mysql_query("ROLLBACK");
}

     

    Basically, only insert_1 gets rolled back.  I thought on my old server the whole set rolled back, but I can't be 100% sure.

     

    Any suggestions?

    Zend Framework SubDir Hostmonster Addon Domain

    in Frameworks

    Posted

    Hello all,

     

    I have my completed site done in zend framework. Everything works fine locally.  It is a very basic installation.

     

    I have an account with hostmonster where I can have unlimited addon domains that point to subdirectories/subdomains.  I dragged my entire contents of the zend installation to the subdir and added a .htaccess snippet to make the pointed domain look in the public folder for the zend installation (index.php).

    I got the rewrite code from here: http://helpdesk.hostmonster.com/index.php/kb/article/347 .

     

    I went to the domain and all that showed was the index page for my main controller (home page).  This page didn't include any of my styling from the web folder or images.  I checked the source and the layout showed as expected.  Also, when I tried to go to any other page I received a 404 error.

     

    It seems that not everything is getting routed correctly.  I can only get to the root domain page (home). And no content from the web folder is showing.  I am not terribly familiar with htaccess and servers.

     

    Has anyone installed zend on a addon domain or subdirectory before?  Any ideas????

     

    Thanks in advance.

    Zend Framework Routing: HTML Extension

    in Frameworks

    Posted

    So i don't know about anyone else, but I think the Zend Framework docs are extremely difficult for new users.  I have done a lot of searching on google for what seems to be a trivial issue.  I would like to add the .html extension onto the routing.  I have one forum that mentions two sites for custom zend routing, but I have tried many methods without any results.

     

    I dont' want to create an ini file.  I would like to use Zend_Controller_Router_Route or Regex version with the front controller.  Everytime i have tried a method to add my route, every page seems to take me directly to controller index action index.  I have deleted the custom routing and tried with the same results.  I also am trying to keep the rest of the routing as it is normally.  Simply, I want to add the extension without changing anything else.

     

    Anyone have this example???

     

    Thanks in advanced/

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.