I sincerely apologize if repost, I can't find an answer. I am new to MySql and PHP and I am trying to write a stored procedure that returns content from two separate tables. The call is essentially this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `grabPage`(t_user VARCHAR(32),
t_page VARCHAR(128))
BEGIN
SELECT formC FROM menuTable WHERE user=t_user;
SELECT formC FROM siteTable WHERE user=t_user AND page=t_page ORDER BY contentID;
END
Is this the wrong way of looking at this? What is the best practice? The reason I want to do this with one stored procedure is so that I only have to make one call to the database in my php. That code looks like this, if I should handle it in the php how would I go about doing that?
$con = mysqli_connect("localhost", "dbusername", "dbpassword", "database");
$query = "CALL grabPage('username', 'pagename')";
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($con, $query);
if (!$result) {
printf("Error: %s
", mysqli_error($con));
exit();
}
$row = array();
while ($row[] = mysqli_fetch_array($result));
mysqli_close($con);
foreach ($row as $htmlOut)
echo $htmlOut['formattedContent'];
Thanks for your help.
The key to what you're trying to do is twofold:
For 1 see the first example here:
CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
# Set value of OUT parameter
SELECT VERSION() INTO ver_param;
# Increment value of INOUT parameter
SET incr_param = incr_param + 1;
END;
You'll note the use of OUT and/or INOUT to indicate output, and INTO to assign SELECT results to the var(s).
For 2 you need to create a session variable on the DB server that you can assign the results to and query to get them back. Take a look at this example in the comments here. It gets pretty wordy so I won't copy and paste, but you should be able to adapt it to your needs. There are also some alternative approaches in the main body of the page.