PHP - Really Noobie Sql Query Problem

im joining a table onto the query to show how many likes a post has but it is displaying the post x amount of times depending on how many likes there are.

I think its something to do with the way im joining onto the pid of the post table:


$post_query = $link->query("SELECT p.*, u.*, l.*
            FROM ".TBL_PREFIX."posts as p
            JOIN ".TBL_PREFIX."users as u
            ON (u.u_username = p.p_poster)
            LEFT JOIN ".TBL_PREFIX."post_likes as l
            ON (l.l_pid = p.p_pid)
            WHERE p_tid = '$tid'
            ORDER BY p.p_time_posted ASC")or die(print_link_error());
    
    $likes = array();
    
    while($post_info = $post_query->fetch())
    {
        
        // SOF LIKE LIST
        if (count($post_info['l_pid']) >= 1)
        {
            $likes[] = $post_info['l_username'];
            $amount_likes = count($likes);
            $slice = array_slice($likes, 0, 4, true);
            $remain = array_slice($likes, 4, $amount_likes, true);
            $remain_num = count($remain);
            $like_list = '';
            
            if($amount_likes > 4)
            {
                for($i=0; $i<=3; $i++)
                {
                    $like_list .= profile_link($likes[$i]).', ';
                }
                $like_list .= 'and '.$remain_num.' other like this'; 
            }
            elseif($amount_likes <= 3)
            {
                for($i=0; $i<3; $i++)
                {
                    $like_list .= profile_link($likes[$i]).' ';
                }
                $like_list .= 'like this';
            }
        }


the likes are stored in their own database table seperate from the posts
any advice?

Im just trying to  get this query to retrieve one peice of data about a user if they are logged in however instead of producing any sort of mysql errors or anything it just stops the page from loading properly. Can anyone help solve this?

here is my query code:
     $query = "SELECT credits FROM users WHERE username = $session->username";
  $res = mysql_query($query) or die (mysql_error());
 $row = mysql_fetch_assoc($res);
  $ans = $res['credits'];

I have a running total that I am pulling from a database that I need to sort by year. I have tried adding WHERE YEAR(datepicker) = '2015' but keep getting an error. Here is the original query that works fine when I run it. Just don't need all the records.   SELECT id, datepicker, startmiles, endmiles, O.totalMiles, car, section, (select sum(totalMiles) FROM vehicle WHERE id <= O.id) 'runningTotal' FROM vehicle O ORDER BY datepicker DESC

Hi all,

Could someone have a look at the stuff below. I am able to show the $_POST variables, but from the moment i use mysqli_real_escape_string it goes wrong ( as in the variable seems empty)


// mysqli connection file is above here
//
$clean_name = mysqli_real_escape_string(trim($_POST['name']));
$clean_email = mysqli_real_escape_string(trim($_POST['email']));
echo 'clean_name = '.$clean_name; //outputs noting
echo 'clean_email = '.$clean_email; //outputs noting

$query_control = "SELECT email FROM my_table WHERE email = $clean_email";
    $result = mysqli_query($dbc, $query_control)or die(mysqli_error($con)); // <-------  spotted: should have been $dbc
    //check for duplicates
    $number = mysqli_num_rows($result);
    echo $number;
    if($number === 0){
        $output = 'valid';
    }else{
        $errormessage .= 'duplicate entry';
    }
}


any help would be welcome

edit-  Oh i spotted some stuff
(after changing it becamce: 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 '' at line 1)  But not able to fix it really.

When I use this query
$sql1="select * from fagudtryk_muskler where latinsk like \"$trimmed%\" order by latinsk UNION ALL
select * from fagudtryk_knogler where latinsk like \"$trimmed%\" order by latinsk";

//-run  the query against the mysql query function
$result=mysql_query($sql1);
//-count  results
$numrows=mysql_num_rows($result);

I get this error

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given

How can I fix this?

Hello everybody. I have a query-question...

I have a form that writes data into the database. BUT... I want to verify that data if it allready exists. There are 3 fields that needs to be checked:

1. Type; <- if yes, check 2.
2. Coordinates1 (+ or - 2 from existing record) <- if yes check 3. If no: insert record and echo: "Mine posted succesfully in the database!"
3. Coordinates2 (+ or - 2 from existing record) <- if yes echo: "There is allready a mine of this type in the database on that location!". If no: insert record and echo: "Mine posted succesfully in the database!"

(One coordinate uses this format: xxxxxx,yyy (Where xxxxxx is coordinates1 and yyy is coordinates2).

A comparison for 1 variable I can find, but not for three and also not how to incorporate the + or - 2.
Any help is greatly appreciated. 

Christiaan

code I have so far:
Code: [Select]
<?
include 'mining_cfg.php';

// html collect variable

$type = $_POST['type'];
$level = $_POST['level'];
$coordinates1 = $_POST['coordinates1'];
$coordinates2 = $_POST['coordinates2'];

if ($type == ".$type") AND if ($coordinates1 == ".$coordinates1") AND if ($coordinates2 == ".$coordinates2"){
echo "Mine posted succesfully!";
} else {
echo "There is allready a mine of this type in the database on that location!";
}

// sql insert into database 
$sql ="INSERT INTO mining(type, level, coordinates1, coordinates2)
VALUES ('".$type."', '".$level."', '".$coordinates1."', '".$coordinates2."')";

//uitvoeren van de query :
if (!($temp = mysql_query($sql,$connection)))
showerror();
 
header ( "Location: http://swr.infiniteserve.com/swr-site/test/post_mining.php" );

?>

Hi

Im just working on a wordpress pugin at the moment and Iw as trying out this piece of code

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->testtable WHERE id = 1");
   
   echo $mylink->id ;

there is data in the table there is an id equal to one but it not coming up with anything

I tried it with anouther pable and it works fine I can for example

$mylink = $wpdb->get_row("SELECT * FROM $wpdb->posts WHERE id = 1");
   
   echo $mylink->post_content ;

That comes up fine with the data

But the first one I dont know why its not working


can anyone help


Thanks

James

How can I get this query to work? I am having problems with the HAVING section of it.


Code: [Select]
SELECT id,price, ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) )
  * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) )
  * sin( radians( lat ) ) ) ) AS distance
FROM fuelstations
HAVING distance < 250 , price > 0
ORDER BY price LIMIT 0 , 60;

Hi,

i'm trying to select some entries from my DB, i don't quite understand what's the problem here, i'm using this:

Code: [Select]
//Customers Online
$query = "SELECT COUNT(*) as Anzahl FROM customers WHERE status = 1";
$queryerg = mysql_query($query) OR die(mysql_error());
while($row = mysql_fetch_array($queryerg)){
  $customers_online = $row[0];
}
Which works fine, now i want to select by country and i'm doing this:

Code: [Select]
//Customers DE
   $query = "SELECT COUNT(*) as Anzahl FROM customers WHERE country = 'de'";
   $queryerg = mysql_query($query) OR die(mysql_error());
   while($row = mysql_fetch_array($queryerg)){
     $customers_de = $row[0];
   }
Which doesn't work? If i print / echo $customers_de i just get a blank value, not even 0 or anything just blank.

Any help ?

I tired something earlier, with no luck...  so I'm taking a few steps back.  I've been working this one complicated page for days...

Here's what used to work:

Code: [Select]
//the current URL
 $page_name = $_SERVER["REQUEST_URI"];

//remove any old limit from query before requesting a new limit
$tmp = array();
foreach ($_GET as $fld => $val)
    if ($fld != 'limit')
         $tmp[] = $fld . '=' . $val;
$page_name = $_SERVER['SCRIPT_NAME'] . '?' . implode('&',$tmp);

//sets the number of records to be displayed on the page
<form>
<select name="link">
<option value="<? echo "$page_name" ?>&amp;limit=25">25 records per page</option>
<option value="<? echo "$page_name" ?>&amp;limit=50">50 records per page</option>
<option value="<? echo "$page_name" ?>&amp;limit=100">100 records per page</option>
</select>
</form>

This grabs the current URL, and clears the &limit= string before a new limit is introduced to avoid:
www.example.com/test.php?sub=math[]&sub[]=science&limit=25&limit=50&limit=100

The problem, is that my URL is already querying an array:
www.example.com/test.php?sub[]=math&sub[]=science

So now, when I apply the form above to this URL set the new limit to 50 records per page the URL actually becomes:
www.example.com/test.php?sub=Array&limit=50
Warning: Invalid argument supplied for foreach() ... on line 147

I guess I've broken the array?

Code: [Select]
//line 147
echo "subject:<BR />";
if(!empty($_GET['sub']))
  foreach($_GET['sub'] as $sub) {
    echo "$sub <BR />\n";
  }
~Wayne

Hey everyone, i keep having this error:
Code: [Select]
Could not connect: 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 'desc,mg,pg) VALUES ('1','0','Snoozy','teata','1','White','atata','atatata'' at line 1
This was just a example as I've been lately just trying and trying with no luck,

The script part is:
$appquery = mysql_query("INSERT INTO applications (aid,cid,name,charactername,gender,race,desc,mg,pg) 

VALUES ('".$_COOKIE['ID']."','$insertid','".$_COOKIE['Username']."','$name','$sex','$race','$description','$mg','$pg')") or die('Could not connect: ' . mysql_error());

I have a table 'combo' with two fields, id and char. I want to populate a table, with the following query which fails. $sql = mysql_query("SELECT id,char FROM combo ORDER BY id DESC LIMIT 28");  I a bit rusty on sql but can't see what is wrong with this. Can anyone help please.

Hi guys,

Im just after a little help here because i cant seem to get my script to work correctly. Basically i have a table full of data and i want to be able to extract a months worth of data but split that data into weeks from within the month.

This is my database structu
Code: [Select]
-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 13, 2011 at 09:13 AM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `time_man`
--

-- --------------------------------------------------------

--
-- Table structure for table `clock_status`
--

CREATE TABLE IF NOT EXISTS `clock_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(24) NOT NULL DEFAULT '0',
  `location` int(4) NOT NULL DEFAULT '0',
  `date_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `amend_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `hours_worked` varchar(25) NOT NULL DEFAULT '',
  `lunch` int(11) NOT NULL DEFAULT '0',
  `break` int(11) NOT NULL DEFAULT '0',
  `sick` char(3) NOT NULL DEFAULT 'NO',
  `holiday` char(3) NOT NULL DEFAULT 'NO',
  `comments` text NOT NULL,
  `auth` tinyint(1) NOT NULL DEFAULT '0',
  `auth_date` date NOT NULL,
  `paid` tinyint(1) NOT NULL DEFAULT '0',
  `pay_rate` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=734 ;

--
-- Dumping data for table `clock_status`
--

INSERT INTO `clock_status` (`id`, `user_id`, `location`, `date_time`, `status`, `amend_time`, `hours_worked`, `lunch`, `break`, `sick`, `holiday`, `comments`, `auth`, `auth_date`, `paid`, `pay_rate`) VALUES
(720, '653467435', 3301, '2011-10-12 04:00:00', 0, '2011-10-12 06:35:00', '9300', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(721, '653467435', 3301, '2011-10-12 06:30:00', 0, '2011-10-12 11:25:00', '17700', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(722, '5030341511007', 3301, '2011-10-13 17:45:00', 0, '2011-10-30 22:10:00', '15900', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'H'),
(723, '653467435', 3301, '2011-10-13 01:15:00', 0, '2011-10-13 07:15:00', '21600', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(724, '653467435', 3301, '2011-10-13 03:15:00', 0, '2011-10-13 05:15:00', '7200', 0, 0, 'NO', 'NO', '', 1, '2011-10-13', 0, 'L'),
(725, '653467435', 3301, '2011-11-11 05:45:00', 0, '2011-11-11 13:20:00', '27300', 0, 0, 'NO', 'NO', '', 1, '2011-11-11', 0, 'L'),
(726, '653467435', 3301, '2011-11-11 01:15:00', 0, '2011-11-11 08:25:00', '3663', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'L'),
(727, '12345678954565456', 3301, '2011-11-11 05:45:00', 0, '2011-11-11 16:25:00', '3662', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'L'),
(728, '50226900', 3301, '2011-11-21 09:30:00', 0, '2011-11-21 17:00:00', '27000', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(729, '5030341511007', 3301, '2011-11-22 09:30:00', 0, '2011-11-11 15:30:00', '21600', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(730, '5030341511007', 3301, '2011-11-22 06:00:00', 0, '2011-11-17 09:20:00', '12000', 0, 0, 'NO', 'NO', '', 1, '2011-11-22', 0, 'H'),
(731, '653467435', 3301, '2011-11-22 06:45:00', 0, '2011-11-22 12:30:00', '20700', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'L'),
(732, '5030341511007', 3301, '2011-11-22 07:45:00', 0, '2011-11-22 15:15:00', '3661', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'H'),
(733, '653467435', 3301, '2011-11-22 04:45:00', 0, '2011-11-22 14:05:00', '33600', 0, 0, 'NO', 'NO', '', 1, '0000-00-00', 0, 'L');

-- --------------------------------------------------------


--
-- Table structure for table `staff`
--

CREATE TABLE IF NOT EXISTS `staff` (
  `person_id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `staff_number` varchar(20) NOT NULL DEFAULT '0',
  `dob` date NOT NULL DEFAULT '0000-00-00',
  `pay_rate` char(1) NOT NULL DEFAULT '',
  `date_amended` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `expiry` text NOT NULL,
  PRIMARY KEY (`person_id`),
  UNIQUE KEY `staff_number` (`staff_number`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`person_id`, `name`, `staff_number`, `dob`, `pay_rate`, `date_amended`, `expiry`) VALUES
(16, 'Person Two', '3456', '1990-06-23', 'H', '2011-06-23 07:40:38', ''),
(17, 'Person Three', '5678', '1986-05-29', 'H', '2011-01-24 12:18:03', ''),
(15, 'Person One', '1234', '1995-11-29', 'L', '2011-01-18 16:59:53', ''),
(47, 'Person four', '50226900', '1980-04-23', 'H', '2011-06-17 10:50:50', ''),
(48, 'Person Two', '653467435', '1980-01-13', 'L', '0000-00-00 00:00:00', '01/02/1982'),
(50, 'Person One', '5030341511007', '1983-07-13', 'H', '0000-00-00 00:00:00', ''),
(52, 'Person Three', '12345678954565456', '2000-02-01', 'L', '0000-00-00 00:00:00', '01/03/2019');

-- --------------------------------------------------------


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
This is the script so far...

Code: [Select]
<?php require_once("../includes/connection.php"); ?>
<?php 
$date1 = '2011-10-28';
$date2 = '2011-11-27'; 
$ident = '3301';

$sql10 = "SELECT WEEK(date_time) as Week, staff.name, clock_status.user_id, sum( clock_status.hours_worked ) AS sum_hours, clock_status.hours_worked, clock_status.amend_time
FROM clock_status, staff
WHERE amend_time >= '$date1' AND amend_time < '$date2' AND clock_status.auth =1
AND clock_status.user_id = staff.staff_number
AND clock_status.location = '3301'
GROUP BY user_id
ORDER BY Week, user_id";  
$query10 = mysql_query($sql10) or die(mysql_error());

?>
<table style="border: 1 solid black;">
<tr>
<th>Staff No</th>
<th>Name</th>
<th>Sum hours</th>
<th>Week 1</th>
<th>Week 2</th>
<th>Week 3</th>
<th>Week 4</th>
<th>Week 5</th>
</tr>
<?php while ($row = mysql_fetch_assoc($query10)) { ?>
<tr>
<td><?php echo $row['user_id'] ;?></td>
<td><?php echo $row['name'] ;?></td>
<td><?php echo $row['sum_hours'] ;?></td>
<td>Week 1 hours here</td>
<td>Week 2 hours here</td>
<td>Week 3 hours here</td>
<td>Week 4 hours here</td>
<td>Week 5 hours here</td>
</tr>
<?php } ?>
</table>
<?php require("../includes/footer.php"); ?>The table should list each person once, with the total hours, then the amount of hours in each week in a different column. As represented in my table.

If someone could help me on this issue i would be eternally grateful...


thanks in advance.

Hi all,
I have a form for users to input posts into their blog. This works fine with the INSERT INTO query in my code. However, when I test it and check the database the entry is going in fine but there is no id attached to it. I'm not really sure how to use an INNER JOIN with an INSERT. As you will see, I have tried to work around it, but I am getting a parameter error "mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given". I suspect this is because the query is not working. I have echoed out the SESSION['name'] variable $whichuser and it works fine. Any help appreciated.
Code: [Select]
<?php
session_start(); // always on the top of the script when user should be logged in
 if(isset($_SESSION['loggedin'])){
 }else{
header("location: index.php");
 }
require('connection.php');

$whichuser=$_SESSION['name'];

if(empty($_POST['title'])){
$title=NULL;
echo "<b>Warning:</b>You haven't given your post a title.</br>";
}else{
$title=mysqli_real_escape_string($connection, trim($_POST['title']));
}
if(empty($_POST['content'])){
$content=NULL;
echo "<b>Warning:</b>You haven't entered any content.</br>";
}else{ 
$content=mysqli_real_escape_string($connection, trim($_POST['content']));
}
if(($title) && ($content)){
$newentry = "INSERT INTO blogposts (title, content, timeofpost) 
VALUES ('$title', '$content', NOW())";
if($newentry){
$join= "SELECT * FROM blogposts INNER JOIN users ON blogposts.post_number = users.username WHERE username='$whichuser'";
if($join){
$entry=mysqli_query($connection, $join) or die(mysqli_error($connection));
}else{
echo "THERE'S A PROBLEM WITH THE QUERY.";
}

while($newpost = mysqli_fetch_array($connection, $entry)){
extract($newpost);
echo "<h3>$title</h3></br>";
echo "$content</br>";
echo "$whichuser</br>";
}
}
}
?>

What I am trying to do is query a database using a loop conditional
and return the data but my code only repeats the first instance multiple times!

My code is below, I have tried to use comments to explain what should be happeing!

$rows = null;
$q1 = "SELECT * FROM table";
$r1 = mysql_query($q1) or die(mysql_error());

$i = 1;
while($i <= 5){

$start = (1000 * $i);
$end = ($start + 1000);

while($a1 = mysql_fetch_array($r1, MYSQL_ASSOC)){
/* query the database where 'start' is  >= 1000
 and where 'end' is <= 2000, this should loop so the next time is
 where 'start' >= 1000 * $i should be 1000 then 2000, 3000 etc...
 and where 'end' should be 2000, 3000, 4000 etc...
*/
if($a1['start'] >= $start && $a1['end'] <= $end){
$rows .= $a1['col1'].':'.$a1['col2']."\n";
}

}
echo nl2br(trim($rows.'<p>'));
$i++;
}

/* The problem is that it always shows multiple instances of the first result only 
where start = 1000 and end = 2000
*/
Any help would be much appreciated!!