SQL queries in vBulletin

[stephenNYC]

Hi, I've looked at a lot of postings here and suspect I am being dense, but I am not quite understanding some things and need a little help.
I added a table (cNews) to my vBulletin db and wrote a php page to INSERT or SELECT but need some help integrating and then calling the variables ($num, $headline, $news) in throughout the site.

I read Brad's 'vBulletin and mySQL' article (https://vborg.vbsupport.ru/showthread.php?t=75207) but still don't where and how to write SELECT, INSERT and UPDATE statements,

Thanks very much
Stephen

Here's my code.

$conn = mysql_connect($server, $userName, $password) or die(mysql_error());
mysql_select_db($db_name,$conn) or die(mysql_error());

$postback = $_POST["postback"];
if (empty($postback)) { //no new input so get last
$sql="SELECT num, headline, news FROM cNews ORDER BY num DESC LIMIT 1";
$result = mysql_query($sql, $conn) or die(mysql_error());
}
else { //insert new data then get last
$headline = $_POST["headline"];
$news = $_POST["news"];
$sql = "INSERT INTO cNews VALUES (num,'".$headline."','".$news."')";
$result = mysql_query($sql, $conn) or die(mysql_error());
$sql="SELECT num, headline, news FROM cNews ORDER BY num DESC LIMIT 1";
$result = mysql_query($sql, $conn) or die(mysql_error());
}

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$num = $row['num'];
$headline = $row['headline'];
$news = $row['news'];
}

[Eikinskjaldi]

Firstly, at the top of your new script write

require_once('global.php');

if the script lives in another directory you must first chdir to where global.php lives then chdir back.

Once you have done that, you have access to the $vbulletin->db object, so you can do all your selects/inserts etc using the usual vb database methods.

Secondly, you can then require_once your script and then either

1) put your table stuff into individual functions and call them

or

2) keep themn as global variables, and where ever you want to use them just put a
gloabl $varname;

at the top of the function that needs to use them.

As to where the hooks should go....how long is a piece of string? it depends on where and how you want to access and view the news items.

[EnIgMa1234]

Heres how select and insert are written.

[sql]$vbulletin->db->query_read("SELECT * FROM " . TABLE_PREFIX . "table ......");
[/sql]

[sql]?vbulletin->db->query_write("INSERT INTO " . TABLE_PREFIX . "table .........");[/sql]

[stephenNYC]

Thanks very much. Those were very helpful replies and I got a lot closer: on my test pages the code writes out correctly, but I am confused about how to make $cUserNewsContent & $cNewsContent into global variables so I can call when needed.
Thanks again
Stephen
code below (lots shorter!)

	$postback = $_POST["postback"];
if ($postback=="yes") { // new input so insert
	$cNewsHeadline = $_POST["headline"];
	$cNewsNews = $_POST["news"];
(num,'".$cNewsHeadline."','".$cNewsNews."')";
echo "1";
}

$NewsResult=$db->query_read("SELECT *  FROM " . TABLE_PREFIX . "cNews ORDER BY num DESC LIMIT 1");
$row = mysql_fetch_assoc($NewsResult);
	}

	$cNewsContent= "<form action='indexTest.php' method='post'><input type='text' name='cNewsHeadline' value='".$row['headline']."'><BR>";
	$cNewsContent=$cNewsContent."<INPUT TYPE='text' NAME='cNewsNews' value='".$row['news']."'><BR><input type='hidden' name='postback' value='yes'>";
	$cNewsContent=$cNewsContent."<INPUT TYPE='submit'></form>";

	$cUserNewsContent= "<table><tr><td>".$row['headline']."</td></tr>";
	$cUserNewsContent=$cNewsContent."<tr><td>"..$row['news']."</td></tr></table>";

[Eikinskjaldi]

Originally Posted by stephenNYC

Thanks very much. Those were very helpful replies and I got a lot closer: on my test pages the code writes out correctly, but I am confused about how to make $cUserNewsContent & $cNewsContent into global variables so I can call when needed.
Thanks again
Stephen

near the top of the php script that you want the variable to appear in, put

require_once("whatever the news php scrit is called.php")


Inside any function or method which needs to use the variables, put

global $variablename;

[stephenNYC]

Thanks again for the reply, but I think I have some of the basics wrong.

I created a page (cNews.php) with the script that creates two variables ($cNewsContent and $cUserNewsContent) and assigns values to them

Then put an include in index.php 'include(cNews.php")'

now, if I write "echo $cNewsContent;" in index.php, the correct data shows at the top of index.php, but I want to be able to call that variable in other places.

Was indexphp the wrong place for ' include(cNews.php")'
thanks
Stephen

[calorie]

Including cNews.php in index.php makes $cNewsContent and $cUserNewsContent available in certain code that follows, but not necessarily in other files such as showthread.php for example. If you want to have $cNewsContent and $cUserNewsContent available on most front-end vB pages, try using the global_start hook.

[stephenNYC]

thanks- got it!
I had tried to avoid the plugins, but this seems good
Stephen