[SOLVED] Pass value from a function into mysql query

[jarvis]

Hi,

 

Am struggling with trying to pass a value from a function into a mysql query.

 

The function:

function get_id() {
$query1 = "SELECT `article_category_id`
FROM `article_categories`
WHERE `category` = 'Features'";
$result1 = mysql_query($query1) or die( "Could not execute SQL query" ); 
$row = mysql_fetch_array ($result1, MYSQL_ASSOC); 
$id = $row['article_category_id'];
#echo $id;
}

 

The query:

$query = "
SELECT DISTINCT articles.article_id, articles.title, LEFT(articles.description, 150) AS abbrev, articles.status, article_associations.article_id, article_categories.category, DATE_FORMAT(articles.date_entered,'%M') as month, DATE_FORMAT(articles.date_entered,'%Y') as year, DATE_FORMAT(articles.date_entered,'%d %M %Y') as date 
FROM articles 
INNER JOIN (article_categories 
		INNER JOIN article_associations ON article_categories.article_category_id = article_associations.article_category_id) ON articles.article_id = article_associations.article_id 
WHERE articles.article_id = article_associations.article_id 
AND article_associations.article_category_id=".get_id()."
AND article_associations.approved = 'Y' 
AND status !='0' 
AND DATE_FORMAT(articles.date_entered,'%Y') = '$year' 
ORDER BY date_entered DESC 
LIMIT 3";

What I need to do is get the id value from the function and pass it into the query. Simple? I cannot get my head around it so any help is very much appreciated right now!

 

Many thanks

[Mark Baker]

function get_id() {
$query1 = "SELECT `article_category_id`
FROM `article_categories`
WHERE `category` = 'Features'";
$result1 = mysql_query($query1) or die( "Could not execute SQL query" ); 
$row = mysql_fetch_array ($result1, MYSQL_ASSOC); 
$id = $row['article_category_id'];
return $id;
}

$query = "
SELECT DISTINCT articles.article_id, articles.title, LEFT(articles.description, 150) AS abbrev, articles.status, article_associations.article_id, article_categories.category, DATE_FORMAT(articles.date_entered,'%M') as month, DATE_FORMAT(articles.date_entered,'%Y') as year, DATE_FORMAT(articles.date_entered,'%d %M %Y') as date 
FROM articles 
INNER JOIN (article_categories 
		INNER JOIN article_associations ON article_categories.article_category_id = article_associations.article_category_id) ON articles.article_id = article_associations.article_id 
WHERE articles.article_id = article_associations.article_id 
AND article_associations.article_category_id=".get_id()."
AND article_associations.approved = 'Y' 
AND status !='0' 
AND DATE_FORMAT(articles.date_entered,'%Y') = '$year' 
ORDER BY date_entered DESC 
LIMIT 3";

 

[JonnoTheDev]

You must return values from functions for them to exist in the global scope.

<?php
function get_id() {
$query1 = "SELECT `article_category_id`
FROM `article_categories`
WHERE `category` = 'Features'";
$result1 = mysql_query($query1) or die( "Could not execute SQL query" ); 
$row = mysql_fetch_array ($result1, MYSQL_ASSOC); 
return $row['article_category_id'];
}
?>

To improve this function you could use the category name as a parameter. The function can then be used to

return the category id of any category. i.e.

<?php
function get_id($category) {
$query1 = "SELECT `article_category_id` FROM `article_categories` WHERE `category` = '".$category."'";
$result1 = mysql_query($query1) or die("Could not execute SQL query"); 
$row = mysql_fetch_array ($result1, MYSQL_ASSOC); 
return $row['article_category_id'];
}

$id = get_id("Features");
?>

[jarvis]

Damn, so simple!

 

Thanks, you rock!!