Databases
StackOverflow2013
Postsdbo
POMySQL query works but not retrieving all information and probably not very efficient
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL query works but not retrieving all information and probably not very efficient
    primarykey
    data
    text
    <p>I am adding a tags feature to a polling system using MySql and PHP. The user will click a tag to list all polls (AKA propositions) with the respective tag. The link looks like this, "/propositions/tagged/baseball".</p> <p>I use a mod_rewrite to assign the "baseball" tag to a variable called $tag.</p> <pre><code>RewriteRule ^propositions/tagged/(.*)$ /propositions/index.php?tag=$1 [L] </code></pre> <p>It does not return the tag names of the tags associated with the poll. How would I get those names?</p> <p>The query below works, but it does not return the tag names of the tags associated with the poll. How would I get those names? Also, can it be done more efficiently. One more thing, would it be better to store the tags with the poll data. I saw a while back where SO does this like including the tags in a table column with the question. Don't know if they still do that or ever did that but saw or read it somewhere a while back.</p> <p>Thanks so much!</p> <p>=======================================================</p> <pre><code>pb_prop ( id int(11) NOT NULL auto_increment, active tinyint(1) NOT NULL default '1', submit_by int(11) NOT NULL, total_votes int(11) NOT NULL default '0', removed tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Described below as ".PROP_TABLE." p </code></pre> <p>=======================================================</p> <pre><code>pb_prop_tags ( id int(11) NOT NULL auto_increment, prop_id int(11) NOT NULL, tag_id int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Described below as ".PROP_TAGS_TABLE." pt </code></pre> <p>=======================================================</p> <pre><code>pb_tags ( tag_id int(11) NOT NULL auto_increment, tag_name varchar(64) NOT NULL, PRIMARY KEY (tag_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Described below as ".TAGS_TABLE." t </code></pre> <p>=======================================================</p> <pre><code>pb_prop_answers ( id int(11) NOT NULL auto_increment, prop_id int(11) NOT NULL, num_votes int(11) NOT NULL default '0', PRIMARY KEY (id), KEY poll_id (prop_id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Described below as ".PROP_ANSWERS_TABLE." a </code></pre> <p>=======================================================</p> <p>Working SQL below ...could be improved? Since posting the original SQL, I added specific columns to the select ...got rid of some wild cards. Also, added more to the WHERE to filter the results better.</p> <p>Original SQL shown after.</p> <pre><code> SELECT u.username, u.userid, p.*, pt.prop_id, pt.tag_id, t.*, ( SELECT SUM(num_votes) FROM ".PROP_ANSWERS_TABLE." a WHERE a.prop_id = p.id ) AS total_votes, ( SELECT count(*) FROM ".PROP_ANSWERS_TABLE." a WHERE a.prop_id = p.id ) AS total_answers FROM ".PROP_TABLE." p INNER JOIN ".TAGS_TABLE." t ON t.tag_name LIKE '%".$tag."%' INNER JOIN ".PROP_TAGS_TABLE." pt ON pt.tag_id = t.tag_id LEFT JOIN ".USERS_TABLE." u ON u.userid = p.submit_by WHERE pt.tag_id = t.tag_id AND pt.prop_id = p.id AND p.removed = 0 AND p.active = 1 AND t.tag_id = pt.tag_id ORDER BY {$sort} {$dir} </code></pre> <p>Original posted SQL:</p> <pre><code> SELECT u.username, u.userid, p.*, pt.*, ( SELECT SUM(num_votes) FROM ".PROP_ANSWERS_TABLE." a WHERE a.prop_id = p.id ) AS total_votes, ( SELECT count(*) FROM ".PROP_ANSWERS_TABLE." a WHERE a.prop_id = p.id ) AS total_answers FROM ".PROP_TABLE." p INNER JOIN ".PROP_TAGS_TABLE." pt ON pt.tag_id = p.id INNER JOIN ".TAGS_TABLE." t ON t.tag_name LIKE '%".$tag."%' LEFT JOIN ".USERS_TABLE." u ON u.userid = p.submit_by WHERE p.removed = 0 AND p.active = 1 AND t.tag_id = pt.tag_id ORDER BY {$sort} {$dir} </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMark
    1. USMark
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL query works but not retrieving all information and probably not very efficient
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload