PHP - Simple Query Not Working...

I have a database called "postvoting", It's basically to store when somebody votes on a particular posts. I store the post_id that the user votes on, the users id that voted on it, and the date.
What I want to do is find the most popular posts in a given time.

So if 4 people voted on the post with the id of 1, and 2 people voted on the post with the id of 2, I want to count the number of rows with post_id='1'

A non working example what I want would look something like:
$count_votes = mysql_query("SELECT * FROM postvoting WHILE post_id=post_id");
print mysql_num_rows($count_votes);
result: 4

or
$count_votes = mysql_query("SELECT * FROM postvoting GROUP BY post_id");
print mysql_num_rows($count_votes);
result: 4

(counting the number of results in a group)

Hope this isn't too confusing. (I've confused myself with this).

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=314391.0

Im sure this is simple, but I cannot see what my problem is!

I am hitting an error on my insert query

Code: [Select]
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
I know the output of $user_id is 1, so my error is on $mysavepath

Code: [Select]
$mysavepath = $folder.'/'.$worldname.'_'.date("dMjY");
echo $mysavepath;
$savepath = mysql_query("INSERT INTO saves (user_id,savepath) VALUES ('$user_id','$mysavepath')");
echo '<br>'.$savepath;
if(!mysql_query($savepath))
{
die('<br>Error: ' . mysql_error());
}

however it all echos out ok?

Code: [Select]
188ea678f0dcdc8252aeb15e3c910408/world_15Jan152012
1
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

Can anyone see the problem?

Cheers
Dave

OK, here it is.  I have been trying to do this myself, but it has been driving me insane and I turn to professionals here for help.   I am a basic web developer for a company that I work for in other capacities. I have a reasonable understanding of HTML and that is about where my expertise ends. I am not typically a programmer, just a simple (extremely) part time designer that uses Muse and Dreamweaver when necessary.   However, recently my company has asked me to accomplish a task for their website. In plain English, they want a large database that exists currently as a CVS file made into a searchable web page.  It is 21 columns by approximately 6,700 rows.   To explain what I need a little more technically, here are my ideas and where I have gotten to so far:   1. The company uses godaddy, into which I *believe* I have successfully imported the spreadsheet. I believe it is successful because through godaddy's SQL Control Panel (phpMyAdmin console), I can do the EXACT searches that the company needs, and it spits out the EXACT results that I need.   2. The end result needs to be a .php that I can upload to the website's root folder that can be then inserted into premade pages using:

<iframe src="SMQ.php" scrolling="yes" width="950" height="800"></iframe>

3. On the .php page, I need to have a way to log in to the SQL server and a simple search box built in that will allow the user to input a very simple search string consisting of no more than 4 numbers and 3 letters at a time. No buttons, no check boxes, just a search box.   3. This query then needs to be output as a nice data table, similar to this: This in fact is a screenshot of a search I performed out of my SQL database, in phpMyAdmin using the column "Scott" for the search, and the number 226 as the search term. All column names are visible with the exception of the first column, entitled LINEID, made to be the key, and the output should not include the key but have everything else as above.   4. I can see what the simple line of php is that performed this task:

SELECT * FROM `SMQSQL` WHERE `Scott` = '226' ORDER BY `LINEID` ASC 

but I can't figure out how the hell to get this incorporated to a .php search.   To sum it up, I need a .php page written that can connect to a SQL database, perform a data based search, and spit out a clean table when it is done.   I had accomplished this in the past using an import into google docs and using it to perform a search and result display via the following code built into a php called SMQ.php:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Example of Google Spreadsheet Data Visualisation</title>
</head>

<body>

<form id="form1" method="post" action ="<?php echo $_SERVER['PHP_SELF']; ?>"> <label>
<input id="search" name="search" type="text" />
</label>
<label>
<input id="Scott #" name="Scott #" type="submit" value="Scott #" />
</label>

<img src="loading.gif" width="16" height="11" />
</form>
<p>
<?php
$search= $_REQUEST['search'];
if ($search > ''){ $search = $search;} else { $search = '';}
?>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {packages: ['table']});
</script>
<script type="text/javascript">
var visualization;
function drawVisualization() {

var query = new google.visualization.Query(
'https://docs.google.com/spreadsheet/ccc?key=0AronCwm9QPefdGpIUllscGgtLUJod2pOazc0bjU0cUE&usp=sharing');

query.setQuery('SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O ,P ,Q ,R ,S ,T WHERE (A) LIKE ("<?php echo $search; ?>") order by A asc label A "Scott #", B "Den", C "Color", D "Cond", E "40", F "60", G "70", H "70J", I "75", J "75J", K "80", L "80J", M "85", N "85J", O "90", P "90J", Q "95", R "95J", S "98", T "98J"');
query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + '' + response.getDetailedMessage());
return;
}

var data = response.getDataTable();

    visualization = new google.visualization.Table(document.getElementById('table'));
    visualization.draw(data, { page: 'enable', page: 16, pageSize: 16, legend: 'bottom'});
}
google.setOnLoadCallback(drawVisualization);
</script>

 <div id="table"></div>

</div>

</body>
</html>

But as you can see, this may not be the most secure thing in the world, plus we want to be able to expand it in the future and not be so simplistic, hence the need to switch to SQL.   Please let me know right away by contacting me at disead@gmail.com if this is something YOU might be able to help with. I'm sure for an experienced programmer, once you have the details from me that you need, it would take maybe 10 minutes to write. I don't have much, but I can pay a little bit for this one time job. If it ends up working out, I may be able to pay more down the line for more advanced options such as being able to do drop-down searches based on the column titled "ISSUE", as well as more things down the line as it grows.   Thank you so much, I hope to hear from someone soon!!!

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=329660.0

The following query or while loop is only increasing the ArticleID variable every 3rd time the script is run, I've narrowed it down to the following code snippet. Can you spot a problem with this, I'm in my first week of PHP and MySQL and I can't see any problem with it.

Any help would be mighty appreciated by this idiot 

Code snippet:
---
$result = mysql_query("SELECT ArticleID FROM test_top ORDER BY ArticleID ASC LIMIT 1") or die(mysql_error()); 
   while($row = mysql_fetch_array($result))
   {
   $ArticleID=$row['ArticleID'];
   }
$ArticleID=intval($ArticleID);
$ArticleID++;
---

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=350117.0

Hi,

very simple i'm sure... i have a code for a text box to pull info into from a mysql table. easy enough:

Code: [Select]
<?php echo "<input type=\"text\" id=\"b_info\" name=\"b_info\" value=\"$b_info\" style=\"width: 327px\"  /text>"; ?>

but suppose i dont just want a "line of text" i want one of those boxes with the scroll bar down the side (lol like the one im writing this in  ) how do i change the above code to do it?

i though doing this:
Code: [Select]
<?php echo "<input type=\"textarea\" id=\"b_info\" name=\"b_info\" value=\"$b_info\" style=\"width: 327px\" style=\"height: 185px\" /textarea>"; ?>

would work, but obviously not, all ive done is make the box bigger 

easy workaround?

Thanks

I'm so sorry for this question but I not really know how to play with single and double quote.

If I have a query like this:

Code: [Select]
  mysql_query('UPDATE table SET Status=1,Sending=Done WHERE ID IN ('.implode(',', $done).')');
And I wish to add Code: [Select]
SentAt='$date' in the query as well , and I try this:

Code: [Select]
  mysql_query('UPDATE table SET Status=1,Sending=Done,SentAt='$date' WHERE ID IN ('.implode(',', $done).')');
Not working...how should I write it?

Thank you.

I'm building a query that searches by database and returns matching (or almost matching) terms.  That part isn't the problem- I have it up and working.  The problem is that I'm trying to narrow down the search results, and it's not working.

Here's the query that works:
Code: [Select]
$result = mysql_query("SELECT * FROM auctions WHERE  name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%'");
Here's the query that DOESN'T works:
Code: [Select]
$result = mysql_query("SELECT * FROM auctions WHERE type='Cars' AND  name LIKE '%".$searchterm."%' OR Address LIKE '%".$searchterm."%' OR state like '%".$searchterm."%'");
What I'm trying to do is say "give me all the results from type:Cars.  Instead, it ignores the WHERE type='Cars' statement, and returns results for all types.  It frustrates me because I use the same exact query in a thousand other places, and it works everywhere else.  For example:

Code: [Select]
$sql = "SELECT * FROM auctions WHERE type='Boats' AND state='$v4' ORDER BY $v1 $v2";
works just fine.  I'm not exactly an expert on any of this, but I can see no logical reason why this works, but the Search code doesn't.  They appear in all ways identical, at least as far as query structure goes.  Can anyone spot where I screwed up?
Thanks!
Kyle

Hello, I am trying to pick up php again and just exercising my skills. So I have it so that it fills my form with the values of what I want to edit, and when I click the edit button, it doesn't edit any of the information. When I echo out $result, I get a MYSQL query string that has the same values as the table, so its not getting the new values that are edited.


<?php

@mysql_connect('localhost', 'root', '') or die("Could not connect to Mysql Server. " . mysql_error());

@mysql_select_db('tutorials') or die("Could not connect to Database. " . mysql_error());

if(isset($_GET['edit']))

{

$id = $_GET['edit'];

$query = "SELECT `username`, `password` FROM `users` WHERE `id` = '$id'";

$result = mysql_query($query);

$row = mysql_fetch_array($result);

$name = $row['username'];

$password = $row['password'];

}

if(isset($_POST['edit']))

{

$id = $_GET['edit'];

$query = "UPDATE `users` SET `username` = '$name', `password` = '$password' WHERE `id` = '$id'";

$result = mysql_query($query);

echo $query;

if(!$result)

{

echo mysql_error();

}else{

echo 'updated post';

}

}
?>
<form method="POST" action="" >

<input type="text" name="name" value="<?php echo $name; ?>" /> First name <br />

<input type="text" name="password" value="<?php echo $password; ?>" /> Last name <br />

<input type="submit" name="edit" value="edit" />
</form>


I believe it has something to do with the values of $name and $password in the form conflicting with the first if isset and the second if isset.

Thanks for any help possible   

Im using the following query to pull all topics that have a new reply since the users last visit and where the user has posted within them.

Code: [Select]
$topic_query = $link->query("SELECT t.*, u.*, p.*
            FROM ".TBL_PREFIX."topics as t
            LEFT JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = t.t_poster)
            LEFT JOIN ".TBL_PREFIX."posts as p
            ON (t.t_tid = p.p_tid)
            WHERE t.t_last_post_time > u.u_activity_time
            AND p.p_poster = '".$user->user_name."'
            GROUP BY t.t_tid
            ORDER BY t.t_sticky DESC, t.t_time_posted DESC
            LIMIT $start_page, $topics_to_show")
            or die(print_link_error());

for some reason though it still shows two topics that have a last post time which is less than that of the users activity time.

The last_post_time for the topic is

1300309160

compare that with the users last activity time

1300784679

as you can see the last activity time is more than the last post time. there are no records in the database that are more than the last activity time so i shouldnt be getting any results.

Anyone have any ideas?

Evening everybody, hoping someone can see my error here!
$sql="INSERT INTO transactions (t_ref, m_affid, m_name, order_val, order_date, order_status, u_id, u_comm) VALUES('$t_ref', '$m_affid', '$m_name', '$order_val', '$order_date', '$order_status', '$u_id', '$u_comm')";
if (!mysql_query($sql))
   { 
    mysql_error();
   }
else

{
$newq = mysql_query("SELECT * FROM userbalance WHERE u_id = '$u_id'");
echo mysql_error();
$row = mysql_fetch_assoc($newq);
$cpending = $row['pending'];
$npending = $cpending + $u_comm;
$updatepending = "INSERT into userbalance (pending)
VALUES ('$npending')
WHERE u_id = '$u_id'";

if (!mysql_query($updatepending))
   { 
    mysql_error();
   }

}

Basically  the 1st query works and inserts the data correctly, however once it gets to $newq = mysql_query("SELECT * FROM userbalance WHERE u_id = '$u_id'"); this query does not insert anything, no errors.

ok Im building a search page.  The user can enter a name, county, category, address into my search field.  The results page will show the details based on that.  It works great so here is a snippet of the working code.




$result = "-1";
if (isset($_POST['searchField'])) {
  $result = $_POST['searchField'];
}



$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat, Category.*, County.*FROM Clubs

INNER JOIN Category ON Clubs.cat = Category.catID  
INNER JOIN County ON Clubs.county = County.countyID 
WHERE
 ((Clubs.name Like %s) OR (Clubs.cat LIKE %s) OR (County.county LIKE %s) OR (Clubs.area LIKE %s) OR (Clubs.postcode LIKE %s) OR (Category.categorys LIKE %s)) "

, String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"));


This code works great no matter what I enter.  the problem Im having is I only want to show results based on the logged in users county.  I have a variable stored under $user['county']  which is the ID found in the County table under countyID

I have tried writing the code so many different ways.  Here is my latest attemped but it doesnt work.  It breaks the whole query and nothing is displayed.



$result = "-1";
if (isset($_POST['searchField'])) {
  $result = $_POST['searchField'];
}

$County= "-1";
if (isset($user['county'])) {
  $County= $user['county'];
}



$result = sprintf("SELECT Clubs.clubID, Clubs.name, Clubs.county, Clubs.logo, Clubs.postcode, Clubs.intro, Clubs.thumbsup, Clubs.cat, Category.*, County.* FROM Clubs
INNER JOIN Category ON Clubs.cat = Category.catID  
INNER JOIN County ON Clubs.county = County.countyID 
WHERE
 ((Clubs.name Like %s) OR (Clubs.cat LIKE %s) OR (County.county LIKE %s) OR (Clubs.area LIKE %s) OR (Clubs.postcode LIKE %s) OR (Category.categorys LIKE %s)) AND County.countyID = %s"

, String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text"),
String($result . "%", "text")
String($County. "%", "int"));


Thanks

Danny

Can someone review this piece of code and advise what's missing that's preventing the SQL query from working?
Issue: no results being echoed out from $results.

Note: I confirmed the db connection  established (only the query not working - no results pulled from db and displayed via echo statements).



$db = mysql_connect($host,$user,$pw)
        or die("Cannot connect to MySQL.");

mysql_select_db($database,$db)
        or die("Cannot connect to database.");

echo "Success!  Connected to database ".$database."<br /><br />";

// $jsearch is variable sent in from form "title" name
$jsearch = $_POST['jobsearch'];

// I confirmed that all the field names are correct

  //-query  the database table
  $sql="SELECT id,title,details,status FROM jobs WHERE title = '$jsearch';";
  //-run  the query against the mysql query function
  $result=mysql_query($sql) or die("<br>Query string: $result<br>Returned error: " . mysql_error() );
  //-create  while loop and loop through result set
  while($row=mysql_fetch_array($result)){
          $title  =$row['title'];
  echo title."<br />";
          $details=$row['details'];
  echo $details."<br />";
  $status=$row['status'];
  echo $status."<br />";
          $ID=$row['id'];