PHP - Variable In Query Works, But Php Does Not

I'm having trouble with a simple SELECT query. I just cannot figure out what the problem is...
<?php
//Include database connection details
include 'login/config.php';

//Connect to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if(!$link) {
die('Failed to connect to server: ' . mysql_error());
}

//Select database
$db = mysql_select_db(DB_DATABASE);
if(!$db) {
die("Unable to select database");
}

$qry="SELECT * FROM members";
$result = mysqli_query($link, $qry);

                                echo "<table>";

while($row = mysqli_fetch_array($result, MYSQL_ASSOC))
{
$getid = ($row['member_ID']);
$firstname = ($row['firstname']);
$lastname = ($row['lastname']);
$email = ($row['email']);

echo "<tr><td>$firstname</td><td>$email</td></tr>"; 
}

echo "</table>";
?>

I know I have a connection to the DB, and I know that the query will return values as I have tested in in phpmyadmin.

Can anyone see anything obvious I am missing?

Thanks

I have a really weird problem.
I'm trying to run a mysql query that works fine in phpmyadmin but in php script is giving me an error.
The query is:

Code: [Select]
(SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('WORD*' IN BOOLEAN MODE) AS score
FROM articles art,galeries gal
WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`)
AGAINST ('WORD* ' IN BOOLEAN MODE))
UNION
(SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`)
AGAINST ('WORD* ' IN BOOLEAN MODE) AS score
FROM articles
WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE)))
ORDER BY score DESC LIMIT 0,30
Snipset from php script code:


function search($start_row,$ammount,$search_key,$pages){
        $start_row = intval($start_row) * $ammount;

        $return_val = "";
        $symbols = array('/','\\','\'','"',',','.','<','>','?',';',':','[',']','{','}','|','=','+','-','_',')','(','*','&','^','%','$','#','@','!','~','`'

);//this will remove punctuation
        $pattern = "#[^(\w|&#945;|&#946;|&#947;|&#948;|&#949;|&#950;|&#951;|&#952;|&#953;|&#954;|&#955;|&#956;|&#957;|&#958;|&#959;|&#960;|&#961;|&#963;|&#964;|&#965;|&#966;|&#967;|&#968;|&#969;|&#913;|&#914;|&#915;|&#916;|&#917;|&#918;|&#919;|&#920;|&#921;|&#922;|&#923;|&#924;|&#925;|&#926;|&#927;|&#928;|&#929;|&#931;|&#932;|&#933;|&#934;|&#935;|&#936;|&#937;|&#962;|&#940;|&#941;|&#972;|&#943;|&#973;|&#974;|&#942;|&#970;|&#971;|&#912;|&#944;|&#902;|&#904;|&#908;|&#906;|&#910;|&#911;|&#905;|&#938;|&#939;|\d|\'|\"|\.|\!|\?|;|,|\\|\/|\-|:|\&|@)]+#";

        $search_key = greek_text::to_upper($search_key);
        $wc = strip_tags($search_key);
        $wc = preg_replace($pattern, " ", $wc);

        for ($i = 0; $i < sizeof($symbols); $i++) {

                $wc = str_replace($symbols[$i],' ',$wc);
        }

        $wc = str_replace("&#900;", " ", $wc);
        $wc = str_replace(chr(162), " ", $wc);

        if( !$keep_numbers ) {
            $wc = preg_replace('#(^|\s+)[\d\s]+(\s+|$)#',' ',$wc);
            $pattern = '#(^|\s+)([0-9]+[a-zA-Z&#945;&#946;&#947;&#948;&#949;&#950;&#951;&#952;&#953;&#954;&#955;&#956;&#957;&#958;&#959;&#960;&#961;&#963;&#964;&#965;&#966;&#967;&#968;&#969;&#913;&#914;&#915;&#916;&#917;&#918;&#919;&#920;&#921;&#922;&#923;&#924;&#925;&#926;&#927;&#928;&#929;&#931;&#932;&#933;&#934;&#935;&#936;&#937;&#962;&#940;&#941;&#972;&#943;&#973;&#974;&#942;&#970;&#971;&#912;&#944;&#902;&#904;&#908;&#906;&#910;&#911;&#905;&#938;&#939;]+\s*)+(\s+|$)#';
            preg_match($pattern,$wc,$aa);
            $wc = preg_replace($pattern, " ", $wc);
        }
        $wc = trim(preg_replace("/\s\s+/", " ", $wc));
        $wc = explode(" ", $wc);
        $cleaned_keyword = array_filter($wc);

        $cleaned_keyword = greek_text::removeStopWordsFromArray($cleaned_keyword);
        $stemmed_keywords = greek_text::stemWordsArray($cleaned_keyword);

        $query = "(SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('";

        while(list($key,$val)=each($stemmed_keywords)){
                if($val<>" " and strlen($val) > 1){
                        $query .= $val."*";
                        $search_keys .= $val."* ";

                }
        }

        $query .= "' IN BOOLEAN MODE) AS score FROM articles art,galeries gal WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))";
        $query .= " UNION (SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE) AS score FROM articles WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT ".intval($start_row).",".$ammount;
                
        $rs = $this->dbActions->execQuery($query);
        $this->dbActions->execQuery("INSERT INTO searches (`KEY`,`DATE`,`RESULTS`) VALUES ('".$search_key."',NOW(),".$pages.")");
        $search_results = "<div id='results'>";
        while($row = mysql_fetch_array($rs)){           
           $search_results.= "<div id='result'>";
           $search_results.= "<div class='result_title'><h4><a href='article.php?articleId=".$row["ARTICLE_ID"]."'>".$row['TITLE']."</a></h4></div>";
           $search_results.= "<div class='result_description'>".$row['DESCRIPTION']."</div>";
           $search_results.= "</div>";
           $search_results.= "<div class='result_seperator'></div>";
        }
        $search_results .= "</div>";
        $return_val = $search_results;

        return $return_val;
    }


dbactions class:


require 'includes/errors.php';
error_reporting(0);

class DBActions{

var $dbCon;

var $errorHandler;

function DBActions(){

$this->dbCon = $this->dbCon();

$errorHandler = new errors(1);

}

private function dbCon(){

require 'conf/configuration.php';

$dbcon = mysql_connect($dbUrl,$dbUser,$dbPass);

if(!$dbcon)trigger_error("Unable to connect to database $dbUrl for user $dbUser",E_USER_ERROR);

mysql_select_db($dbName);

mysql_query("SET NAMES 'UTF8'");

mysql_query('set character set utf8');

return $dbcon;

}

function dbClose(){

if($this->dbCon)

mysql_close($this->dbCon);

}

function execQuery($query){

              
                $result = mysql_query($query,$this->dbCon);
                                               

$msg = "Unable to execute query ".$query;
                if(mysql_num_rows($result) > 0)

return $result;

else if($result == false)

trigger_error($msg,E_USER_ERROR);

else

return $result;

}

function send_error_mail(){

$this->errorHandler->sendErrorEmail();

}
}


I've try everything but can't get this working..
Any healp whould be really appreciated.

Thanks in advance.

Hi, this query runs fine when I run it from PHPMyAdmin:
 

UPDATE `tran_term_taxonomy` SET `description` = (SELECT keyword from `good_keywords` ORDER BY RAND() LIMIT 1,1) WHERE `tran_term_taxonomy`.`taxonomy` = 'post_tag' AND `tran_term_taxonomy`.`description` = "" LIMIT 1

However, when I run the same query in a PHP file on my server, the page doesn't load at all. The message I get is: www.somesite.com is currently unable to handle this request. HTTP ERROR 500. This is my PHP code:

<?php 
include("/database/connection/path/db_connect.php"); 

$result4 = mysqli_query($GLOBALS["___mysqli_ston"], "UPDATE `tran_term_taxonomy` SET `description` = (SELECT keyword from `good_keywords` ORDER BY RAND() LIMIT 1,1) WHERE `tran_term_taxonomy`.`taxonomy` = 'post_tag' AND `tran_term_taxonomy`.`description` = "" LIMIT 1");
echo $result4;

?>

So how do I make this query work please? Thanks for your guidance.

Hi, I'm new to PHP/MySQL and need some help getting my query to work for my selection list:

The selection list is built with:
<form action='processformmissing.php' method='POST'>
<fieldset>
<legend>Choose Department</legend>
<select name='depart'>
<option value=''></option>

<?php
while ($row = mysqli_fetch_array($result))
{
extract($row);
echo "<option value='$department'>$department</option>\n";
}
?>
</select>
<p><input type='submit' value='Select Department' /></p>
</fieldset>

</form>

The data is then sent to:

$depart = $_POST['depart'];
$deptlike = "%".$depart."%";

echo "<p>$depart</p>";
echo "<p>$deptlike</p>";

$query = "SELECT * FROM lifecerts INNER JOIN employees ON lifecerts.cid = employees.cid WHERE department LIKE '$deptlike' ORDER BY employees.name";



Hitting the submit button from my selection list form seems to be working fine because when I echo my data ($depart and $deptlike) it is giving me the correct value, but the query doesn't give me any results. However, if my post data comes from a text box instead of a selection list, my query works fine. Any thoughts on what I'm doing wrong???

Many thanks!

hi i have the script below which copies data from one table to another but will only insert new data update current data or delete old data from tempproducts to products then it will delete the tempproducts from the db however i keep getting this error:

Warning: mssql_query() [function.mssql-query]: Query failed in E:\UpdateProducts.php on line 33

updateproducts.php
Code: [Select]
<?php
include('../../otherscripts/functions.php');
$log = new Logging(); 

// create DB connection
 $host = "localhost";
 $user = "user";
 $pass = "pass";
 $mydb = "db";

 $db = mssql_connect($host,$user,$pass);
 
//Select Database
 mssql_select_db($mydb);
 

 // delete all old data
    $sql0 = "SELECT * FROM tempproduct";
    $sql1 = "INSERT INTO products SELECT * FROM tempproduct WHERE manf_part_no NOT IN (SELECT manf_part_no FROM products) AND supp_id NOT IN (SELECT supp_id FROM products)";
    $sql2 = "DELETE FROM products WHERE manf_part_no NOT IN (SELECT manf_part_no FROM tempproduct) AND supp_id NOT IN (SELECT supp_id FROM tempproduct)";
    $sql3 = "UPDATE p1 SET p1.avail_qty = t1.avail_qty, p1.cost_price = t1.cost_price, p1.rrp = t1.rrp, p1.date_added = t1.date_added, p1.description = t1.description FROM Products p1 INNER JOIN tempproduct t1 ON (p1.manf_part_no = t1.manf_part_no AND p1.supp_id = t1.supp_id)";
    $sql4 = "TRUNCATE TABLE tempproduct";

//If tempproduct is empty done Execute Commands if it is full then execute commands

$query = mssql_query($sql0) or die($log->lwrite('Failed to select for count from db'));
$rowcount = mssql_num_rows($query);

if($rowcount == 0){
$log->lwrite('Teh tempproduct am emptyish');
}
else{
   mssql_query($sql1) or die($log->lwrite('Failed to insert to db'.$sql1));
   mssql_query($sql2) or die($log->lwrite('Failed to Delete from db'));
   mssql_query($sql3) or die($log->lwrite('Failed to Update db'));
   mssql_query($sql4) or die ($log->lwrite('Failed to TRUNCATE db'));
}
?>

if i run $sql1 command in the sql manager it runs fine and no errors occur?

I have this variable that I need to get into my database and I just can't think through it. Here is the code.


$sql = "INSERT INTO names (id, name, color, uploaddate) VALUE (NULL,'$master[2]['dbid']','$color','$time')";


I know I need to escape those quotes around dbid somehow, but I can't seam to get it figured out.  If I have to I will set it = to a different variable, but I don't want to do it that way if I don't have to

 

$id = '9';

$MyQuery = "SELECT * FROM bookings WHERE id = '$id'";
$retrieve = mysql_query($MyQuery) or die(mysql_error());
if(mysql_num_rows($retrieve) != 0):
$row = mysql_fetch_assoc($retrieve);
else:
echo 'No Information';
endif;

echo 'Full Name: ' . ($row['fullname']) . '<br><br>';

The above code is working for me however when I change it to what I do...

$id = ($row['id']);

It tells me I have an undefined row variable... What will fix this? Thanks in advance...

okay i have table named ZAMJENE with 2 rows only row that i need to setup is named id_zamjena and have values 15 and 16

when i do echo on page it work great but when i press button i get only 16 for both entries

full cde is too long so i paste only important parts

$query_zamjene = mysql_query ("SELECT * FROM zamjene  WHERE id_user_1='$moj_id' AND zamjenjeno='0' ORDER BY id_event_1");

echo "<form method='post' action='event_zamjenjen.php'>";


while ($rows = mysql_fetch_array($query_zamjene))
{
$id_zamjena = $rows['id_zamjena'];
$id_event_1 = $rows['id_event_1'];
$id_event_2 = $rows['id_event_2'];
$id_user_2 = $rows['id_user_2'];

echo "$id_zamjena";   // THIS ROW WORKS CORRECTLY
echo "<input name='id_zamjena' type='hidden' value=$id_zamjena>";  // output of this is always 16

echo "<input name='response' type='submit' value='Prihvati zamjenu' />
<input name='response' type='submit' value='Odbaci zamjenu' />
<br>";

}
echo"</form>";

I got my search page error and the server pop up below message:   Notice: Undefined variable: query in /home/tz005/public_html/COMP1687/search.php on line 64
Minimum length is 3   Where should I make a correction in the script? Here is my php script:   <?php  

Hello,
So I have a content management site set up, but I'm stuck on one part. I have a query. Code: [Select]
Select `email` FROM author WHERE author.id IN(SELECT authorid FROM job WHERE id = '$_POST[id]')
I'm a beginner in PHP and SQL. Basically I need the query to run, it will come out with one result and I need that to be where the email is sent to.

This is the existing code I have for sending out the email $subject = " Job Application Confirmation";

$message = "Hello $_POST[name] , \r\rYou, or someone using your email address, has applied for the job: $text Using the Resource Locator at Prahan.com. Keep this as future reference. \r\r Full Name: $_POST[name] \r Location: $_POST[location] \r Email: $_POST[email] \r Additional Information: $_POST[info] \r Job ID: $id  \r Job Name: $text  \r \r Expect a response shortly. \r\r Regards, Prahan.com Team";

$headers = 'From: Prahan.com Team <noreply@prahan.com>';

    'Reply-To: noreply@prahan.com' . "\r\n" .

    'X-Mailer: PHP/' . phpversion();

$to1      = 'pratik@prahan.com';

mail($to1, $result, $message, $headers);


Thanks in Advance!

Hi Guys.

I need your help. Is it possible to store results from sql query as a variable. I am basically doing a few joins to get this result and I wanted to know if this was possible.

Hi

This does not make sense to me, I hope someone can tell me why this query don't work:

Code: [Select]
safe_query("UPDATE ".PREFIX."cup_challenges SET reply_date='$postdate', $select_map $select_date challenged_info='".$_POST['info']."', status='2' WHERE chalID='".$_GET['challID']."'");;
and this query does work:

Code: [Select]
safe_query("UPDATE ".PREFIX."cup_challenges SET reply_date='$postdate', map1='".$_POST['map1']."', map2='".$_POST['map2']."', map3='".$_POST['map3']."', date1='".$_POST['date1']."', date2='".$_POST['date2']."', date3='".$_POST['date3']."', date4='".$_POST['date4']."', challenged_info='".$_POST['info']."', status='2' WHERE chalID='".$_GET['challID']."'");

Variables in $select_map and $select_date makes the query fail but when I copy/paste this (bold) in query it works:

$select_map = map1='".$_POST['map1']."', map2='".$_POST['map2']."', map3='".$_POST['map3']."',
$select date = date1='".$_POST['date1']."', date2='".$_POST['date2']."', date3='".$_POST['date3']."', date4='".$_POST['date4']."',

am I using the variable in query incorrectly?

i have a small search profile search and need to set $getuname variable to users username

if((trim($country) !== ''&&($state) !== ''&&($city) !== '')){


   $data = mysql_query("SELECT    *  FROM users WHERE users.state='$state' AND users.country='$Country' AND users.city='$city' ORDER BY RAND() DESC LIMIT 1 ");   
Echo "Country city state search";
$getuname = mysql_real_escape_string($_GET['username']);

am i meant to use the get or is that only for forms?

Hi there,
im trying to have a form show up when user clicks "add joke". I need the variable to be retrieved from the url query string. I cant get the form to show up.  I think its either an issue with the GET function at the top or the link down at the bottom. Please help!


<?php
  // If the user wants to add a joke
  
    $_GET['addjoke'] = $addjoke;

  if (isset($addjoke)):
?>

<FORM ACTION="<?php echo($PHP_SELF); ?>" METHOD=POST>
<P>Type your joke he <BR>
<TEXTAREA NAME="joketext" ROWS=10 COLS=40 WRAP>
</TEXTAREA><BR>
<INPUT TYPE=SUBMIT NAME="submitjoke" VALUE="SUBMIT">
</FORM>

<?php
  else:

    // Connect to the database server
    $dbcnx = @mysql_connect("servername", "username", "password");
    if (!$dbcnx) {
      echo( "<P>Unable to connect to the " .
            "database server at this time.</P>" );
      exit();
    }

    // Select the jokes database
    if (! @mysql_select_db("jhodara2") ) {
      echo( "<P>Unable to locate the joke " .
            "database at this time.</P>" );
      exit();
    }

    // If a joke has been submitted,
    // add it to the database.

$joketext = $_POST['joketext']; 
$submitjoke = $_POST['submitjoke']; 

    if ("SUBMIT" == $submitjoke) {
      $sql = "INSERT INTO jokes SET " .
             "JokeText='$joketext', " .
             "JokeDate=CURDATE()";
      if (mysql_query($sql)) {
        echo("<P>Your joke has been added.</P>");
      } else {
        echo("<P>Error adding submitted joke: " .
             mysql_error() . "</P>");
      }
    }
  
    echo("<P> Here are all the jokes " .
         "in our database: </P>");
  
    // Request the text of all the jokes
    $result = mysql_query(
              "SELECT JokeText FROM jokes");
    if (!$result) {
      echo("<P>Error performing query: " .
           mysql_error() . "</P>");
      exit();
    }
  
    // Display the text of each joke in a paragraph
    while ( $row = mysql_fetch_array($result) ) {
      echo("<P>" . $row["JokeText"] . "</P>");
    }
  
    // When clicked, this link will load this page
    // with the joke submission form displayed.
    echo("<P><A HREF='$PHP_SELF?addjoke=1'>Add a Joke!</A></P>");
  
  endif;
  
?>


see the problem live at http://www.freewaycreative.com/insert2.php

Can anyone point out how to write a MySQL query with a PHP variable in the WHERE clause. I've tried {} {'xx'} and () and it still doesn't work.

Here is the code



<?php
ini_set('display_errors',1);
error_reporting(E_ALL|E_STRICT);

 include ("include/connect.php");
 include ("include/session.php");

$username = $session->userinfo['username'];

$result = mysql_query("SELECT email FROM customer WHERE user = {'$username'} ");

while($row = mysql_fetch_array($result))
  {
$custemail = $row['email'];
  }

echo "Session username: " . $username . "";
echo "Session customer email: " . $custemail . "";
?>



So I'm trying to show the email address for a record that matches the username of the user logged in.

I really appreciate the help.