Another PDO problem

  • (6 Pages)
  • +
  • 1
  • 2
  • 3
  • 4
  • Last »

77 Replies - 1365 Views - Last Post: 07 December 2013 - 04:03 AM Rate Topic: ***-- 2 Votes

#16 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 30 October 2013 - 10:08 AM

I have fixed it now.But one very annoying thing which I don't know how to stop is the fact that it now doesn't give all the info out.

http://downloads.str...=Stronghold%202

I think it may be something to do with the CSS, but I don't know.

What you are actually seeing below the "total downloads" option should be right of the "kingmaker" category, and I don't know how to fix it.

I can either get it to echo one category correctly, or it doesn't do any right, apart from getting the list of categories down the bottom.


Don't really update the title, it was kind of humour on my part (not done very well though).

This post has been edited by chris98: 30 October 2013 - 10:47 AM

Was This Post Helpful? 0
  • +
  • -

#17 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 30 October 2013 - 04:13 PM

without the code I canít say anything.
Was This Post Helpful? 0
  • +
  • -

#18 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 02:01 AM

if ($action == 'display')
		{
		echo '
		  <table class="upload" cellspacing="0" cellpadding="0" border="0" width="100%">
		<colgroup><col width="30%"><col width="20%"><col width="15%"><col width="35%"></colgroup>
		<tr>
		<th class="files">Category</th>
		<th>Total Downloads</th>
		<th>Updated</th>
		<th>Last File</th>
		</tr>
		<tr class="upload">
		';


$sql4 = "SELECT category FROM download_categories WHERE site = :site";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql4);
$ps->execute(array(':site'=>$site));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
echo '
<td class="upload">
  
  <a href="lister.php?category='.$row['category'].'&site='.$site.'"><b>'.$row['category'].'</a>
</td></p>';
	






$sql5 = "SELECT COUNT(*) AS id FROM downloads WHERE  category = :category AND site = :site";
$ps = $pdo->prepare($sql5);
$ps->execute(array(':site'=>$site,
':category'=>$category));
foreach ($ps as $row)
{  echo "

		<td class='upload'><b>".$row['id']."</b></td>";
}


$sql7 = "SELECT date FROM `downloads` WHERE category = :category AND site = :site ORDER BY id DESC LIMIT 1";
$ps = $pdo->prepare($sql7);
$ps->execute(array(':site'=>$site,
':category'=>$category));
foreach ($ps as $row) 
if ($row['date'] > 1)
{  
echo "<td class='upload'><b>".$row['date']."</b></td>";
}
else
{
echo "<td class='upload'><b>N/A</b></td>";
}


$sql7 = "SELECT name,id FROM `downloads` WHERE category = :category AND site = :site ORDER BY id DESC LIMIT 1";
$ps = $pdo->prepare($sql7);
$ps->execute(array(':site'=>$site,
':category'=>$category));
foreach ($ps as $row) 
{  echo "

		<a href='downloads/viewfile.php?fileid=".$row['id']."'>".$row['name']."</a></td>
		</tr>";
}
?>

<?php } ?>
<?php } ?>



Sorry, I thought I had posted the code already.

I don't now think it is something with the CSS, because whenever I change it to this, it lists all five categories, but I can't get it to display all columns for all categories.

if ($action == 'display')
		{
		echo '
		  <table class="upload" cellspacing="0" cellpadding="0" border="0" width="100%">
		<colgroup><col width="30%"><col width="20%"><col width="15%"><col width="35%"></colgroup>
		<tr>
		<th class="files">Category</th>
		<th>Total Downloads</th>
		<th>Updated</th>
		<th>Last File</th>
		</tr>
		<tr class="upload">
		';


$sql4 = "SELECT category FROM download_categories WHERE site = :site";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql4);
$ps->execute(array(':site'=>$site));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
echo '
<td class="upload">
  
  <a href="lister.php?category='.$row['category'].'&site='.$site.'"><b>'.$row['category'].'</a>
</td></p>';
	

		</tr>";
?>

<?php } ?>
<?php } ?>


This post has been edited by chris98: 31 October 2013 - 02:04 AM

Was This Post Helpful? 0
  • +
  • -

#19 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 02:37 AM

Right, I have now managed to get this.But every time I try to add to it, and make variables out of the "test" and "test2".etc, it just goes back to what it did before, and doesn't echo what it should.

EDIT: Another very annoying thing is that it won't let me assign new variables (in the are marked).

I try with something like:

$row['category'] = $category;

and it doesn't work.It just echo's nothing.


if ($action == 'display')
		{
		echo '
		  <table class="upload" cellspacing="0" cellpadding="0" border="0" width="100%">
		<colgroup><col width="30%"><col width="20%"><col width="15%"><col width="35%"></colgroup>
		<tr>
		<th class="files">Category</th>
		<th>Total Downloads</th>
		<th>Updated</th>
		<th>Last File</th>
		</tr>
		<tr class="upload">
		';


$sql4 = "SELECT category FROM download_categories WHERE site = :site";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql4);
$ps->execute(array(':site'=>$site));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
//IT WON'T LET ME ASSIGN NEW VARIABLES HERE
echo '
<td class="upload">
  
  <a href="lister.php?category='.$row['category'].'&site='.$site.'"><b>'.$row['category'].'</a>
</td>
<td class="upload">
  
  <b>test1</b>
</td>


<td class="upload">
  
  <b>test1</b>
</td>

<td class="upload">
  
  <a href="viewfile.php?fileid='.$row['category'].'"><b>FILENAME</a>
</td>
</tr>';
}
?>
<?php } ?>


This post has been edited by chris98: 31 October 2013 - 02:47 AM

Was This Post Helpful? 0
  • +
  • -

#20 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 31 October 2013 - 07:49 AM

Quote

But every time I try to add to it, and make variables out of the "test" and "test2"

what for? where do you feed those variables with content?

besides that, why did the code become more scattered and complex than it was before?

whatís up with line #44/#45?
Was This Post Helpful? 0
  • +
  • -

#21 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 08:00 AM

I wanted to make the variables out of "test1" and "test2" because I am replacing the total downloads in that category with "test1" and when the category was last updated for "test2".But I can't actually assign a variable, as whenever I try, It doesn't echo ANY category on the page, and I really don't understand why - every other select statement I code seems to let me - it's just that one.


With line 44/45 whenever I "end the database coding" (as I see it) I sometimes do break out and in to PHP again, as I feel it helps me to find it, and whenever I look at it, I know instantly that it is the end of the database selection.I feel it helps me to spot the end easier, I don't know why I do it really.

For example, I feel that:
}

}
?>



is harder to spot as being the end of the database selection, than say:

}
?>
<?php } ?>



As the second stands out more. (It's probably just me)
Was This Post Helpful? 0
  • +
  • -

#22 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 31 October 2013 - 08:21 AM

Quote

With line 44/45 whenever I "end the database coding" (as I see it) I sometimes do break out and in to PHP again, as I feel it helps me to find it, and whenever I look at it, I know instantly that it is the end of the database selection.I feel it helps me to spot the end easier, I don't know why I do it really.

how about
// end of database section

?

in most IDEs pretty well to spot because of the colouring.

besides that,
}
?>
<?php } ?>

prints a line break. it may not matter now, but if you ever have code that is supposed to modify the HTTP headers afterwards, it wonít work because of that.

Quote

As the second stands out more. (It's probably just me)

if you work with templates or frameworks, you wonít have a single piece of HTML in your script.

Quote

But I can't actually assign a variable, as whenever I try, It doesn't echo ANY category on the page, and I really don't understand why - every other select statement I code seems to let me - it's just that one.

since there ainít any code to demonstrate that failure, I cannot say what you did wrong. although it strikes me mysterious, how you want to get a variable out of a query, that doesnít even provide any values for it.
Was This Post Helpful? 0
  • +
  • -

#23 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 08:34 AM

Quote

how about

// end of database section



?


That's actually a better idea.I'll do that instead.

What actually are templates? Is it a template of the entire page, or just of s certain section of it?

And here is the current code I have:

<?php

if ($action == 'display')
		{
		echo '
		  <table class="upload" cellspacing="0" cellpadding="0" border="0" width="100%">
		<colgroup><col width="30%"><col width="20%"><col width="15%"><col width="35%"></colgroup>
		<tr>
		<th class="files">Category</th>
		<th>Total Downloads</th>
		<th>Updated</th>
		<th>Last File</th>
		</tr>
		<tr class="upload">
		';


$sql4 = "SELECT category FROM download_categories WHERE site = :site";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql4);
$ps->execute(array(':site'=>$site));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
echo '
<td class="upload">
  
  <a href="lister.php?category='.$row['category'].'&site='.$site.'"><b>'.$row['category'].'</a>
</td>
<td class="upload">
  
  <b>test1</b>
</td>

<td class="upload">
  
  <b>test2</b>
</td>

<td class="upload">
  
  <a href="viewfile.php?fileid='.$row['category'].'"><b>FILENAME</a>
</td>
</tr>';
}
// End of DB
} ?>



Right, now as you will see, the "test1" and "test2" above.But somewhat frustratingly, as soon as I place a PDO statement in it (I.E. o get the total downloads in that category) then it doesn't echo any category at all.I really don't understand it at all.

Here is the link if you want to take a look at it currently:

http://downloads.str...=Stronghold%202

(If you tell me once you have looked, I will change it so you will see the difference - none of the links on the left will appear, as soon as I change it to something like below)

if ($action == 'display')
		{
		echo '
		  <table class="upload" cellspacing="0" cellpadding="0" border="0" width="100%">
		<colgroup><col width="30%"><col width="20%"><col width="15%"><col width="35%"></colgroup>
		<tr>
		<th class="files">Category</th>
		<th>Total Downloads</th>
		<th>Updated</th>
		<th>Last File</th>
		</tr>
		<tr class="upload">
		';


$sql4 = "SELECT category FROM download_categories WHERE site = :site";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql4);
$ps->execute(array(':site'=>$site));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) {
$row['category'] = $category;
echo '
<td class="upload">
  
  <a href="lister.php?category='.$row['category'].'&site='.$site.'"><b>'.$row['category'].'</a>
</td>
<td class="upload">';

$sql5 = "SELECT COUNT(*) AS id FROM download_categories WHERE site = :site AND category = :category";
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$ps = $pdo->prepare($sql5);
$ps->execute(array(':site'=>$site,
':category'=>$category));
while ($row = $ps->fetch(PDO::FETCH_ASSOC)) { 
echo '
  <b>'.$row['id'].'</b>
</td>

<td class="upload">
  
  <b>test2</b>
</td>

<td class="upload">
  
  <a href="viewfile.php?fileid='.$row['category'].'"><b>FILENAME</a>
</td>
</tr>';
}
// End of DB
 } ?>



EDIT: In fact, even just placing the $row['category'] = $category; stops it from echoing the categories.

This post has been edited by chris98: 31 October 2013 - 08:39 AM

Was This Post Helpful? 0
  • +
  • -

#24 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 31 October 2013 - 09:27 AM

queries inside queries can be problematic, esp. here. and it totally goes against what prepared statements are.

prpared statements are prepared exactly once, and then you can feed them any data you want, as often as you want. (only ->execute() should ever be placed inside a loop).

before we untie the knot, there need some things to be clarified.
- what is the relation between `site` and `category` (e.g. can there be more that one category (site) per site (category) ?
=> it may well be that you can get the info with just one query
Was This Post Helpful? 0
  • +
  • -

#25 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 09:37 AM

The site table has the list of all the different games, and sections of the downloads.

The sites are the first thing that you choose, so you can only have one the same.

There can be categories under the same names, as long as they are under a different site.

I.E.

I couldn't have two kingmaker categories under Stronghold 2.

kingmaker stronghold 2
kingmaker stronghold 2

Here is the SQL:

CREATE TABLE `download_sites` (
  `site_title` varchar(100) NOT NULL,
);




Whereas the categories are actually in a different table:

CREATE TABLE `download_categories` (
  `id` int(11) NOT NULL auto_increment,
  `category` varchar(100) NOT NULL,
  `site` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`)
);



Inside each site, there are multiple categories from the table categories.So for example, in the Stronghold 2 (from the sites table), I check to see if there are any categories in the categories table with the site of "Stronghold 2".

So an example of a site would be Stronghold 2.

SITES TABLE (each line represents a different row)

SITE_TITLE

stronghold 2
stronghold crusader

etc.etc.etc

CATEGORIES TABLE

ID CATEGORY SITE

1 kingmaker Stronghold 2
2 Custom War Stronghold 2
3 Castle Builder Stronghold Crusader

etc.etc.etc

This post has been edited by chris98: 31 October 2013 - 09:38 AM

Was This Post Helpful? 0
  • +
  • -

#26 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 31 October 2013 - 09:42 AM

so a category can hold different sites, but a site can only belong to a single category?

if I understand that right, you want the count of sites per category. that would be just
SELECT COUNT(sites) AS amount, category FROM download_categories GROUP BY category

This post has been edited by Dormilich: 31 October 2013 - 09:44 AM

Was This Post Helpful? 0
  • +
  • -

#27 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 09:49 AM

The other way round.

The sites can hold different categories. (It's pretty complicated)

Dormilich said:

if I understand that right, you want the count of sites per category. that would be just
SELECT COUNT(sites) AS amount, category FROM download_categories GROUP BY category



It's a bit trickier than that.I have actually managed to get the categories in the sites already, and displayed them on the initial index.

But, now I want to display:

A.) The number of files inside a particular category, inside a particluar site

I.E. The number of download files currently in the kingmaker category in the stronghold 2 site.

B.) The time a file was last updated in a particular category, in a particular site.

I.E. Basically the date of the last file uploaded in (for example) the category kingmaker of the stronghold 2 site.

C.) Link to the last file that was uploaded in that category.

The files are in a seperate downloads table, called downloads.

(The table categories are like sub-categories)

This post has been edited by chris98: 31 October 2013 - 09:54 AM

Was This Post Helpful? 0
  • +
  • -

#28 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 31 October 2013 - 10:02 AM

A ) SELECT COUNT(files), site, category FROM download_categories GROUP BY category, site ORDER BY category or if you want to go for specifics
SELECT COUNT(files) FROM download_categories WHERE category = ? AND site = ?

B ) SELECT file, file_modified FROM download_categories WHERE category = ? AND site = ? ORDER BY file_modified DESC LIMIT 1

C ) SELECT file FROM download_categories WHERE category = ? ORDER BY file_modified DESC LIMIT 1
Was This Post Helpful? 0
  • +
  • -

#29 chris98  Icon User is offline

  • D.I.C Addict

Reputation: 3
  • View blog
  • Posts: 557
  • Joined: 06-July 13

Re: Another PDO problem

Posted 31 October 2013 - 10:10 AM

The files are in a seperate table altogether.

There is the categories table, the files table & the sites table.

CREATE TABLE `downloads` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `username` varchar(30) NOT NULL,
  `userid` varchar(300) NOT NULL,
  `size` varchar(100) NOT NULL,
  `text` text NOT NULL,
  `minimap` varchar(30) default NULL,
  `date` varchar(30) NOT NULL,
  `file` varchar(100) default NULL,
  `category` varchar(50) NOT NULL,
  `email` varchar(100) default NULL,
  `difficulty` varchar(11) NOT NULL,
  `balanced` varchar(12) NOT NULL,
  `estates` varchar(5) NOT NULL,
  `support` varchar(100) default NULL,
  `missions` varchar(6) NOT NULL,
  `updated` varchar(30) default NULL,
  `up_by` varchar(30) default NULL,
  `edit` varchar(10) default NULL,
  `edit_group_id` varchar(50) default NULL,
  `approval` varchar(50) default NULL,
  `tags` varchar(100) default NULL,
  `site` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
);



I want to find info about the last file from downloads that has the site of X and the category of Y, the total records that have site = X and category of Y.

But when I try something like this for the count, it doesn't echo the categories correctly. (Which is probably because it won't let me define $category)

$sql5 = "SELECT COUNT(*) AS id FROM downloads WHERE  category = :category AND site = :site";
$ps = $pdo->prepare($sql5);
$ps->execute(array(':site'=>$site,
':category'=>$category));
foreach ($ps as $row)
{  echo "

<td class='upload'>
  
  <b>".$row['id']."</b>
</td>";
}


Was This Post Helpful? 0
  • +
  • -

#30 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3392
  • View blog
  • Posts: 9,586
  • Joined: 08-June 10

Re: Another PDO problem

Posted 31 October 2013 - 10:42 AM

Quote

The files are in a seperate table altogether.

nothing that couldnít be fixed by a JOIN.


Quote

But when I try something like this for the count, it doesn't echo the categories correctly.

inside the loop, $category is a static variable. and tbh, I donít see why you should have more than one result row. COUNT() is an aggregate function.
Was This Post Helpful? 0
  • +
  • -

  • (6 Pages)
  • +
  • 1
  • 2
  • 3
  • 4
  • Last »