Oh no! Where's the JavaScript?
Your Web browser does not have JavaScript enabled or does not support JavaScript. Please enable JavaScript on your Web browser to properly view this Web site, or upgrade to a Web browser that does support JavaScript.
Not a member yet? Click here to register.
Forgot Password?
  1. Home
  2. Discussion Forum
  3. PHPFusion 9 Support
  4. Modifications and Requests - 9
  5. Help wanted: DB Query

Help wanted: DB Query

Asked Modified Viewed 1,414 times
Back to Forum Index Previous Topic Next Topic
Print
C
Catzenjaeger
C
  • Senior Member, joined since
  • Contributed 408 posts on the community forums.
  • Started 137 threads in the forums
  • Started this discussions
asked
Senior Member

Hola i add in my first PHPF9 figurelib Infusion some stats and need help for some DB queries.

I guess some are wrong .. when a user add a figure i see all double7
i want to show the expensivest figure and the figure with the highest used price

Code Download source
$resultteuerste = dbquery("
                     SELECT 
                        tb.figure_id, tb.figure_submitter, tb.figure_retailprice, tb.figure_usedprice, tb.figure_freigabe, tb.figure_pubdate, tb.figure_scale, tb.figure_title, tb.figure_manufacturer, tb.figure_brand, tb.figure_datestamp, tb.figure_cat, 
                        tbc.figure_cat_id, tbc.figure_cat_name, 
                        tbu.user_id, tbu.user_name, tbu.user_status, tbu.user_avatar, 
                        tbm.figure_manufacturer_name, 
                        tbb.figure_brand_name,                      
                        tbs.figure_scale_id, tbs.figure_scale_name,                      
                        fuf.figure_userfigures_figure_id, fuf.figure_userfigures_user_id 
                     FROM ".DB_FIGURE_ITEMS." AS tb
                     LEFT JOIN ".DB_USERS." AS tbu ON tb.figure_submitter=tbu.user_id
                     LEFT JOIN ".DB_FIGURE_USERFIGURES." AS fuf ON fuf.figure_userfigures_figure_id=tb.figure_id
                     LEFT JOIN ".DB_FIGURE_CATS." AS tbc ON tb.figure_cat=tbc.figure_cat_id
                     LEFT JOIN ".DB_FIGURE_MANUFACTURERS." AS tbm ON tbm.figure_manufacturer_id = tb.figure_manufacturer
                     LEFT JOIN ".DB_FIGURE_BRANDS." AS tbb ON tbb.figure_brand_id = tb.figure_brand
                     LEFT JOIN ".DB_FIGURE_SCALES." AS tbs ON tbs.figure_scale_id = tb.figure_scale
                     WHERE ".(multilang_table("FI") ? "tb.figure_language='".LANGUAGE."' AND" : "")." tb.figure_freigabe='1' 
                     ORDER BY tb.figure_retailprice DESC LIMIT 0,5

                  ");
         


in my second query i want to show all images from a user in a slideshow.

here my table for images

Quote

$inf_newtable[] = DB_FIGURE_USERFIGURES." (
figure_userfigures_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
figure_userfigures_figure_id VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_user_id VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_purchase_price VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_bought_at VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_buy_where VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_sale_price VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_shipping_costs VARCHAR(100) NOT NULL DEFAULT '',
figure_userfigures_for_sale TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
figure_userfigures_for_trade TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
figure_userfigures_sold TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
figure_userfigures_language VARCHAR(50) NOT NULL DEFAULT '".LANGUAGE."',
PRIMARY KEY (figure_userfigures_id)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8 COLLATE=utf8_unicode_ci";


here my try

Code Download source
$resultimage = dbquery("
            SELECT 
               fu.user_id, 
               fu.user_name, 
               fu.user_status, 
               fu.user_avatar, 
               fuf.figure_userfigures_figure_id,
               fuf.figure_userfigures_user_id ,
               fi.figure_images_image_id,
               fi.figure_images_figure_id,
               fi.figure_images_image,
               fi.figure_images_thumb                
            FROM ".DB_FIGURE_IMAGES." fi ON fi.figure_images_figure_id='".$figure_id."' 
            INNER JOIN ".DB_FIGURE_USERFIGURES." 
            INNER JOIN ".DB_USERS." fu ON fuf.figure_userfigures_user_id='".$user_id."'
            ");
Edited by Catzenjaeger on 27-08-2016 12:08,
0 replies

2 posts

C
Chan
C
Chan 0
Lead Developer of PHP-Fusion
  • Super Admin, joined since
  • Contributed 3,842 posts on the community forums.
  • Started 232 threads in the forums
  • Answered 6 questions
answered
Super Admin

Show Highest Price figure

Code Download source

$resultteuerste = dbquery("
                     SELECT 
                        tb.figure_id, tb.figure_submitter, tb.figure_retailprice, tb.figure_usedprice, tb.figure_freigabe, tb.figure_pubdate, tb.figure_scale, tb.figure_title, tb.figure_manufacturer, tb.figure_brand, tb.figure_datestamp, tb.figure_cat, 
                        tbc.figure_cat_id, tbc.figure_cat_name, 
                        tbu.user_id, tbu.user_name, tbu.user_status, tbu.user_avatar, 
                        tbm.figure_manufacturer_name, 
                        tbb.figure_brand_name,                      
                        tbs.figure_scale_id, tbs.figure_scale_name,                      
                        fuf.figure_userfigures_figure_id, fuf.figure_userfigures_user_id 
                     FROM ".DB_FIGURE_ITEMS." AS tb
                     LEFT JOIN ".DB_USERS." AS tbu ON tb.figure_submitter=tbu.user_id
                     LEFT JOIN ".DB_FIGURE_USERFIGURES." AS fuf ON fuf.figure_userfigures_figure_id=tb.figure_id
                     LEFT JOIN ".DB_FIGURE_CATS." AS tbc ON tb.figure_cat=tbc.figure_cat_id
                     LEFT JOIN ".DB_FIGURE_MANUFACTURERS." AS tbm ON tbm.figure_manufacturer_id = tb.figure_manufacturer
                     LEFT JOIN ".DB_FIGURE_BRANDS." AS tbb ON tbb.figure_brand_id = tb.figure_brand
                     LEFT JOIN ".DB_FIGURE_SCALES." AS tbs ON tbs.figure_scale_id = tb.figure_scale
                     WHERE ".(multilang_table("FI") ? "tb.figure_language='".LANGUAGE."' AND" : "")." tb.figure_freigabe='1' 

GROUP BY tb.figure_id

ORDER BY tb.figure_retailprice DESC

 LIMIT 0,5

                  ");


Show by highest used price in another SQL.

Code Download source

ORDER BY tb.figure_usedprice DESC


Show sort and get the figure by highest figure price and used price first.
Code Download source

ORDER BY tb.figure_retailprice DESC, tb.figure_usedprice DESC
0 replies
C
Catzenjaeger
C
  • Senior Member, joined since
  • Contributed 408 posts on the community forums.
  • Started 137 threads in the forums
  • Started this discussions
answered
Senior Member

cool thank you sooo much .. :G

can plz take a look on this :

Quote

"Global: The figure, which most be in the user's possession."
Code Download source
$resultmostusercount = dbquery("
                    SELECT 
                        tb.figure_id, tb.figure_submitter, tb.figure_retailprice, tb.figure_usedprice, tb.figure_freigabe, tb.figure_pubdate, tb.figure_scale, tb.figure_title, tb.figure_manufacturer, tb.figure_brand, tb.figure_datestamp, tb.figure_cat, 
                        tbc.figure_cat_id, tbc.figure_cat_name, 
                        tbu.user_id, tbu.user_name, tbu.user_status, tbu.user_avatar, 
                        tbm.figure_manufacturer_name, 
                        tbb.figure_brand_name, 
                        tbs.figure_scale_id, tbs.figure_scale_name,                             
                        fuf.figure_userfigures_figure_id, fuf.figure_userfigures_user_id,
                        count(fuf.figure_userfigures_user_id) AS users_counter
                    FROM ".DB_FIGURE_ITEMS." AS tb
                    LEFT JOIN ".DB_USERS." AS tbu ON tb.figure_submitter=tbu.user_id
                    LEFT JOIN ".DB_FIGURE_USERFIGURES." AS fuf ON fuf.figure_userfigures_figure_id=tb.figure_id
                    LEFT JOIN ".DB_FIGURE_CATS." AS tbc ON tb.figure_cat=tbc.figure_cat_id
                    LEFT JOIN ".DB_FIGURE_MANUFACTURERS." AS tbm ON tbm.figure_manufacturer_id = tb.figure_manufacturer
                    LEFT JOIN ".DB_FIGURE_BRANDS." AS tbb ON tbb.figure_brand_id = tb.figure_brand
                    LEFT JOIN ".DB_FIGURE_SCALES." AS tbs ON tbs.figure_scale_id = tb.figure_scale
                    WHERE ".(multilang_table("FI") ? "tb.figure_language='".LANGUAGE."' AND" : "")." tb.figure_freigabe='1' 
                    GROUP BY fuf.figure_userfigures_figure_id
                    ORDER BY users_counter DESC LIMIT 0,5");


Quote

and this: show all images by a user

Code Download source
    $resultimage = dbquery("
                SELECT
                   fu.user_id,
                   fu.user_name,
                   fu.user_status,
                   fu.user_avatar,
                   fuf.figure_userfigures_figure_id,
                   fuf.figure_userfigures_user_id ,
                   fi.figure_images_image_id,
                   fi.figure_images_figure_id,
                   fi.figure_images_image,
                   fi.figure_images_thumb                
                FROM ".DB_FIGURE_IMAGES." fi ON fi.figure_images_figure_id='".$figure_id."'
                INNER JOIN ".DB_FIGURE_USERFIGURES."
                INNER JOIN ".DB_USERS." fu ON fuf.figure_userfigures_user_id='".$user_id."'
                ");
0 replies

Category Forum

Modifications and Requests - 9

Labels

None yet

Statistics

  • Views 0 views
  • Posts 2 posts
  • Votes 0 votes
  • Topic users 2 members

2 participants

C
C
  • Senior Member, joined since
  • Contributed 408 posts on the community forums.
  • Started 137 threads in the forums
  • Started this discussions
C
C
Chan 0
Lead Developer of PHP-Fusion
  • Super Admin, joined since
  • Contributed 3,842 posts on the community forums.
  • Started 232 threads in the forums
  • Answered 6 questions

Notifications

Track thread

You are not receiving notifications from this thread.

Related Questions

Not yet