Creating SQL through a function

[KemuriSka]

Hi,

How do you create an SQL string with a function, ie:

// Something like this:
function createSQL($forum) {
  $sql = "SELECT * FROM ".$forum;
}

Then I would call it with something like:

createSQL("forum_one");

I've tried constructing the SQL in the function like:

$sql = "SELECT * FROM ".$forum; // and...
$sql = "SELECT * FROM $forum"; // and...
$sql = "SELECT * FROM '$forum'"; // and...
$sql = "SELECT * FROM '".$forum."'";

...and calling the function like:

createSQL("forum_one"); // and...
createSQL(forum_one); // and...
$f = "forum_one";
createSQL($f);

...but none seem to work. Could someone tell me where I'm going wrong?

Thanks
Ste

[zhabala]

Show us the code, where it does not work. I am missing some return statement in the createSQL function, but I have to see whole code.

[jowatkins]

Don't know if this will work, but...

function create_sql($table)
{
    $sql = "SELECT * FROM '$table'";
    return $sql;
}

and then call it with

$query = create_sql("forum");

you could even make it more useful by expanding the function as so...

function create_sql($table, $field="", $filter="")
{
    if ( (empty($field)) &&  (empty($filter)) )
    {
        $sql = "SELECT * FROM '$table'";
    }
    else
    {
         $sql = "SELECT * FROM '$table' WHERE $field = '$filter";
    }
    return $sql;
}

call in exactly the sam way as the field and filter parameters are optional. Only add them to your function call if you need them

[KemuriSka]

What this is for is the front page for a forum, and I want to get all of the details from the last post, ie name, time and also how many posts there are in that forum.
It all works outside a function but I can't get it to work inside one. Anyway, here's the function:

function getDetails($forum) {
  $sql = "SELECT * FROM ".$forum;
  $result = @mysql_query($sql);
  if ($result) {
    $numPosts = @mysql_num_rows($result);
    $sql .= " ORDER BY date DESC LIMIT 0, 1";
    $result = @mysql_query($sql);
    if ($result) {
      while ($row = @mysql_fetch_array($result)) {
        $name = $row["name"];
        $dateTime = $row["date"];
      }
      if ($numPosts > 0) {
        $name = eregi_replace(" "," ",$name);
        $year = substr($dateTime,2,2);
        $month = substr($dateTime,4,2);
        $date = substr($dateTime,6,2);
        $hour = substr($dateTime,8,2);
        $min = substr($dateTime,10,2);
        $output = "<p>".$name."</p>\n<p>".$date."-".$month."-".$year."<br>".$hour.":".$min."&nbsp;GMT</p>";
      }else{
        $numPosts = 0;
        $output = "<p align=\"center\">-</p>";
      }
    }else{
      $numPosts = 0;
      $output = "<p align=\"center\">-</p>";
    }
  }else{
    $numPosts = 0;
    $output = "<p align=\"center\">-</p>";
  }
}

And I would call and use the function something like:

getDetails("forum_one");
echo "Posts: ".$numPosts;
echo "Last post: ".$output;

It's a problem with constructing the SQL query, or calling the function (or both?) I just want it all in a function so I don't have a heap load of code to deal with.

Thanks
Ste

[stevesweetland]

id be inclined to do things a little differently.

function getfieldvalues($table){
  $sql = "SELECT * FROM $table ORDER BY ID DESC";
  $resultset = @mysql_query($sql);
  $fielddata = array();
  $fielddata = @mysql_fetch_array($resultset);  

  return $fielddata;
};

of course, id advise you check this first. i dont use mysql_fetch_array, ever. im assuming its going to do what you want it to!!! haha

you may then say:

$foruminfo = getfieldvalues("forum_one");

print "Row Count = ".count($foruminfo )."<br />";
print "Last Post = ".$foruminfo ["timeposted"][0]."<br /";

if im just talking rubbish anyone out there, feel free to just jump in.... i tend to do database objects, which is infinitely easier to do this kind of thing...

PS:
as a side point, variables you create inside a function are not available to code outside of the function unless you specifically return that value.

[stevesweetland]

alternatively, move your print statements to inside the function, and just call the function, which will retreive data, and print it out for you, exactly at the point you call the function. thingy.

function getDetails($forum) {
  $sql = "SELECT * FROM ".$forum;
  $result = @mysql_query($sql);
  if ($result) {
    $numPosts = @mysql_num_rows($result);
    $sql .= " ORDER BY date DESC LIMIT 0, 1";
    $result = @mysql_query($sql);
    if ($result) {
      while ($row = @mysql_fetch_array($result)) {
        $name = $row["name"];
        $dateTime = $row["date"];
      }
      if ($numPosts > 0) {
        $name = eregi_replace(" "," ",$name);
        $year = substr($dateTime,2,2);
        $month = substr($dateTime,4,2);
        $date = substr($dateTime,6,2);
        $hour = substr($dateTime,8,2);
        $min = substr($dateTime,10,2);
        $output = "<p>".$name."</p>\n<p>".$date."-".$month."-".$year."<br>".$hour.":".$min."&nbsp;GMT</p>";
      }else{
        $numPosts = 0;
        $output = "<p align=\"center\">-</p>";
      }
    }else{
      $numPosts = 0;
      $output = "<p align=\"center\">-</p>";
    }
  }else{
    $numPosts = 0;
    $output = "<p align=\"center\">-</p>";
  }

  echo "Posts: ".$numPosts;
  echo "Last post: ".$output;
};

print "forum header stuff<br><br>";

getDetails("forum_one");

print "forum footer stuff";

get the idea?

[KemuriSka]

I can't just echo out the values inside the function because I need them separate, here is a more recent attempt:

function getDetails($forum) {
  $sql = "SELECT * FROM ".$forum;
  $result = @mysql_query($sql);
  if ($result) {
    $numPosts = @mysql_num_rows($result);
    $sql .= " ORDER BY date DESC LIMIT 0, 1";
    $result = @mysql_query($sql);
    if ($result) {
      while ($row = @mysql_fetch_array($result)) {
        $name = $row["name"];
        $dateTime = $row["date"];
      }
      if ($numPosts > 0) {
        $name = eregi_replace(" "," ",$name);
        $year = substr($dateTime,2,2);
        $month = substr($dateTime,4,2);
        $date = substr($dateTime,6,2);
        $hour = substr($dateTime,8,2);
        $min = substr($dateTime,10,2);
        $lastPost = "<p>".$name."</p>\n<p class=\"imageType\">".$date."-".$month."-".$year."<br>".$hour.":".$min."&nbsp;GMT</p>\n\n";
      }else{
        $numPosts = 0;
        $lastPost = "<p align=\"center\">-</p>\n\n";
      }
    }else{
      $numPosts = 0;
      $lastPost = "<p align=\"center\">-</p>\n\n";
    }
      }else{
    $numPosts = 0;
    $lastPost = "<p align=\"center\">-</p>\n\n";
  }
  return $numPosts;
  return $lastPost;
}

getDetails("forum1");
echo $numPosts;
echo $lastPost;

// Method used to output values
getDetails("forum1");
echo $numPosts;
echo $lastPost;

...it must be something to do with returning the values because if I echo them out instead of returning them inside the function, it works.

Ste

[zhabala]

You can't have more than one return statemen in the function, but you can return an array.

Other thing is, that when some function is returning something, it has to be returned into some variable like that:

$some_variable = some_function ( some_parameter )

then returned value is stored into $some_variable.

[KemuriSka]

How do you return an array?

[zhabala]

Same way as other variable.

function something($parameter) {

  $myArray = array();
  $myArray[] = 1;
  $myArray[] = 2;
  $myArray[] = $parameter;

  return $myArray;
}

$otherArray = something('someValue');

echo $otherArray[1];
print_r($otherArray);

[KemuriSka]

Ignore my last post, I found it:

function getDetails($forum) {
  $sql = "SELECT * FROM ".$forum;
  $result = @mysql_query($sql);
  if ($result) {
    $numPosts = @mysql_num_rows($result);
    $sql .= " ORDER BY date DESC LIMIT 0, 1";
    $result = @mysql_query($sql);
    if ($result) {
      while ($row = @mysql_fetch_array($result)) {
        $name = $row["name"];
        $dateTime = $row["date"];
      }
      if ($numPosts > 0) {
        $name = eregi_replace(" "," ",$name);
        $year = substr($dateTime,2,2);
        $month = substr($dateTime,4,2);
        $date = substr($dateTime,6,2);
        $hour = substr($dateTime,8,2);
        $min = substr($dateTime,10,2);
        $lastPost = "<p>".$name."</p>\n<p class=\"imageType\">".$date."-".$month."-".$year."<br>".$hour.":".$min."&nbsp;GMT</p>\n\n";
      }else{
        $numPosts = 0;
        $lastPost = "<p align=\"center\">-</p>\n\n";
      }
    }else{
      $numPosts = 0;
      $lastPost = "<p align=\"center\">-</p>\n\n";
    }
      }else{
    $numPosts = 0;
    $lastPost = "<p align=\"center\">-</p>\n\n";
  }
  return array($numPosts, $lastPost);
}

$output = getDetails("forum1");
echo $output[0];
echo $output[1];

Finally.......

Thanks for all of your help, I've appreciated it 🙂

[rklapwijk]

I know this doesn't answer your question, but maybe it's something to think about. You could use this method:
Save in the forums table the forum title,description,last topic, last poster, date of post and time of post. Then you just have to collect the id of the last topic that has been posted in to link to it, and the number of posts.

This will loop all forums, so you don't have to call a function for each forum.

<?php
$forumdata = mysql_query("SELECT id,title,description,lasttopic,lastposter,date,time FROM forum_forums ORDER BY position");

while ($forumrow = mysql_fetch_row($forumdata)) {
$id = $forumrow[0];
$title = $forumrow[1];
$description = nl2br($forumrow[2]);
$last_topic = $forumrow[3];
$last_user = $forumrow[4];
$last_date = $forumrow[5];
$last_time = $forumrow[6];

if ($last_topic == "") $last_topic = "-";
if ($last_user == "") $last_user = "-";
if ($last_date == "") $last_date = "-";
if ($last_time == "") $last_time = "-";

$forumposts = mysql_query("SELECT id FROM forum_posts WHERE forum_id = '$id'");
$total_posts = mysql_numrows($forumposts);

$forumtopics = mysql_query("SELECT id FROM forum_topics WHERE forum_id = '$id' ORDER BY last_post DESC");
$total_topics = mysql_numrows($forumtopics);
$topicrow = mysql_fetch_row($forumtopics);
?>
    <tr> 
       <td><a href="index.php?fid=<?php echo $id;?>"><?php echo $title;?></a><br><?php echo $description;?><br></td>
       <td><?php echo $total_topics;?></td>
       <td><?php echo $total_posts;?></td>
       <td><?php echo $last_date;?>, <?php echo $last_time;?><br>In: <a href='index.php?fid=<?php echo $id;?>&tid=<?php echo $topicrow[0];?>'><?php echo $last_topic;?></a><br>By: <a href='user.php?user=<?php echo $last_user;?>'><?php echo $last_user;?></a></td>
    </tr>
<?php
}
?>

[stevesweetland]

Originally posted by stevesweetland

function getfieldvalues($table){
  $sql = "SELECT * FROM $table ORDER BY ID DESC";
  $resultset = @mysql_query($sql);
  $fielddata = array();
  $fielddata = @mysql_fetch_array($resultset);  

  return $fielddata;
};

$foruminfo = getfieldvalues("forum_one");

print "Row Count = ".count($foruminfo )."<br />";
print "Last Post = ".$foruminfo ["timeposted"][0]."<br /";

withdrawn sarcastic comment

[KemuriSka]

well maybe if you explained things a bit better.....................