is this possible with mysql?

Discuss news styles, css code, and other Coranto customizations in here...the possibilities are endless, so have at it!

Moderator: Spunkmeyer

is this possible with mysql?

Postby jmaraz » Mon Oct 14, 2002 1:36 am

ok... i think this might be possible to someone with mysql knowledge... but i'm trying to use sub cats and filter by fields all of a sudden isn't working so i'm wondering if its possible to fetch news items from the database only if a specific column or whatever u call it has a value of something... for example.... if the pc_news field = 1 in the sql database then is it possible to use a code to call just those items? i know how call for example the last 5 news items in a database but i've never been able to figure out if you can call items only if a specific field is equal to something. hopefully someone can tell me if it IS possible and even better how to do it.
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby AeroSoul » Mon Oct 14, 2002 1:55 am

SELECT field_name FROM table_name WHERE field_name = field_value
Keep the dream alive!
User avatar
AeroSoul
 
Posts: 562
Joined: Sat Mar 09, 2002 10:30 pm
Location: Toronto, Canada

Postby jmaraz » Mon Oct 14, 2002 2:39 am

cool i will try this soon and post whether it worked or not
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby Sumaleth » Mon Oct 14, 2002 4:32 am

You could use the FilterByField addon:

http://www.sumaleth.com/coranto/cra_filterbyfield.zip

It filters a profile by whether a selected field is on/off or filled/empty.
Sumaleth
 
Posts: 1366
Joined: Thu Jul 11, 2002 4:31 pm

Postby jmaraz » Mon Oct 14, 2002 5:15 am

i was actually going to but after i installed the build 30 thing filter by field wasn't filtering anything... i couldn't figure it out so i just gave up. :roll:
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby jmaraz » Mon Oct 14, 2002 5:51 am

ok... as for the mysql code:

here is the code i am using:

Code: Select all
<?
mysql_connect(localhost, phantasy_news, ******);
mysql_select_db("phantasy_news");
$query = mysql_query("SELECT * FROM coranto WHERE category = gamecube ORDER BY newstime DESC LIMIT 0, 30 ");
while($result = mysql_fetch_array($query)){ echo "<br />"; echo $result['Subject'] ;

}
mysql_close();
?>


i dunno if i have something wrong but I am getting this error:

Code: Select all
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/phantasy/public_html/pages/navbars/right.php on line 32


If i take away the WHERE category = gamecube then it works fine... the whole WHERE thing is causing problems... is my code wrong or something? i'd appreciate any help at all cause i suck with mysql
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby Sumaleth » Mon Oct 14, 2002 3:21 pm

I just tried FilterByField with 30rc2 and it worked fine.
Sumaleth
 
Posts: 1366
Joined: Thu Jul 11, 2002 4:31 pm

Postby AeroSoul » Mon Oct 14, 2002 3:46 pm

Code: Select all
<?
mysql_connect(localhost, phantasy_news, ******);
mysql_select_db("phantasy_news");
$query = mysql_query("SELECT * FROM coranto WHERE category = \"gamecube\" ORDER BY newstime DESC LIMIT 0, 30 ");
while($result = mysql_fetch_array($query)){ echo "<br />"; echo $result['Subject'] ;

}
mysql_close();
?>
Keep the dream alive!
User avatar
AeroSoul
 
Posts: 562
Joined: Sat Mar 09, 2002 10:30 pm
Location: Toronto, Canada

Postby jmaraz » Mon Oct 14, 2002 4:32 pm

yes! finally my troubles are over. thanks so much aerosoul!
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby jmaraz » Mon Oct 14, 2002 9:53 pm

one more question... i bet it probably isn't possible with my sql... but can u make it so that it only shows the items in which two fields are required to be filled... for example it would only show the gamecube news headlines and not the gamecube reviews headlines or something... so the category would be filled into gamecube and a news checkbox would be checkd.

something like this (although i'm sure this code isn't correct at all):

Code: Select all
WHERE category = \"gamecube\" & customfield_news =\"1\"


is this possible? and if so what is the code for it?

i'm pretty sure it ISN'T possible... coranto probably needs table for each category to get this to work... would that be hard to do?
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby AeroSoul » Mon Oct 14, 2002 11:02 pm

WHERE category = \"gamecube\" AND customfield_news = 1

you may need to place escaped double quotes around that '1'
Keep the dream alive!
User avatar
AeroSoul
 
Posts: 562
Joined: Sat Mar 09, 2002 10:30 pm
Location: Toronto, Canada

Postby jmaraz » Mon Oct 14, 2002 11:03 pm

awesome... i was expecting a "no its not possible" but great! thanks again

sql is saving me the trouble of like 50 news profiles and styles
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby jmaraz » Tue Oct 15, 2002 11:15 pm

another sql question... this one i have doubts about...

is it possible to snip the amount of characters that u get from a called item from the database... i know that phpmyadmin manages to snip the text so i was wondering if there was a command they are using. if so that'd be great
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Postby AeroSoul » Tue Oct 15, 2002 11:33 pm

Code: Select all
SELECT LEFT(field_name, 5)

say 'field_name' is 'foofoobar' then this would return the first 5 characters ('foofo')

so in your case it would be something like
Code: Select all
SELECT LEFT(field_name,5) FROM table_name WHERE field_name = field_value

alternativelly you can use the php solution or if you want to get a certain number of words (so it won't cut off in the middle of a word) use:
Code: Select all
SELECT SUBSTRING_INDEX(field_name,' ',50) FROM table_name WHERE field_name = field_value


the ' ' is what you wish to index, in this case a space. '50' is the amount of words you wish to keep - kevin
Keep the dream alive!
User avatar
AeroSoul
 
Posts: 562
Joined: Sat Mar 09, 2002 10:30 pm
Location: Toronto, Canada

Postby jmaraz » Wed Oct 16, 2002 12:57 am

ok i hate to be a bother but this is givin me troubles... whenever i use that code u posted to snip the fields nothign shows up... what would the full code be to get for example, the text field to show up on the page... the code i'm using right now is:

Code: Select all
<?mysql_connect(localhost, phantasy_news, ******);
mysql_select_db("phantasy_news");
$query = mysql_query("SELECT LEFT(Text, 5) FROM coranto WHERE category = \"(default)\" AND customfield_gamecube= \"1\" ORDER BY newstime DESC LIMIT 0, 1 ");
while($result = mysql_fetch_array($query)){

echo $result['Text'] ;

}  ?>


I don't think its working cause I'm using echo to display it... how should i be doing it?
jmaraz
 
Posts: 54
Joined: Mon May 27, 2002 12:00 am
Location: Ontario, Canada

Next

Return to Customization

Who is online

Users browsing this forum: No registered users and 2 guests

cron