Perl cgi sql need help

Question

DB structure:

$dbh->do("DROP TABLE IF EXISTS catalog"); $dbh->do("CREATE TABLE catalog (". " id int(11) unsigned NOT NULL auto_increment,". " surname varchar(50) NOT NULL DEFAULT '',". " name varchar(50) NOT NULL DEFAULT '',". " comment varchar(1250) NOT NULL DEFAULT '',". " height varchar(5) NOT NULL DEFAULT '',". " bust varchar(5) NOT NULL DEFAULT '',". " talia varchar(5) NOT NULL DEFAULT '',". " bedra varchar(5) NOT NULL DEFAULT '',". " shoes varchar(5) NOT NULL DEFAULT '',". " hair varchar(50) NOT NULL DEFAULT '',". " lang varchar(250) NOT NULL DEFAULT '',". " educ varchar(250) NOT NULL DEFAULT '',". " m_code varchar(50) NOT NULL DEFAULT '',". " hidden int(1) unsigned NOT NULL DEFAULT 0,". " PRIMARY KEY (id)". ");" ); $dbh->do("DROP TABLE IF EXISTS catalog_links"); $dbh->do("CREATE TABLE catalog_links (". " pid int(1) unsigned NOT NULL DEFAULT 0,". " cid int(11) unsigned NOT NULL DEFAULT 0,". " sort int(11) unsigned NOT NULL DEFAULT 0,". " PRIMARY KEY (pid,cid)". ");" ); $dbh->do("DROP TABLE IF EXISTS images"); $dbh->do("CREATE TABLE images (". " id int(11) unsigned NOT NULL auto_increment,". " pid int(11) unsigned NOT NULL DEFAULT 0,". " ptype varchar(10) NOT NULL DEFAULT '',". " ext varchar(10) NOT NULL DEFAULT '',". " name varchar(50) NOT NULL DEFAULT '',". " main int(1) unsigned NOT NULL DEFAULT 0,". " sort int(11) unsigned NOT NULL DEFAULT 0,". " PRIMARY KEY (id)". ");" );

Query the DB itself:

 my $hash = {}; if ($pid) { my @items = (); my $sth = $dbh->prepare("SELECT c.*, i.id as im_id, i.ext FROM catalog c, catalog_links cl, images i WHERE c.hidden=0 AND c.id=cl.cid AND (i.pid=c.id AND i.ptype=? AND i.main=1) AND cl.pid=? ORDER BY cl.sort DESC"); my $rvs = $sth->execute('catalog', $pid) or die "execute: ".$sth->errstr."\n"; while (my $item = $sth->fetchrow_hashref) { $item->{lang} =~ s/,/<br>/g; push(@items, $item); }

Thank you very much for the answer. But I still do not have enough understanding how to do this further. As I understand it, in my code from the database, strings that belonged to the same model were taken. Further, the taken lines turned into @items or $ item. Then this code was used to breakdown (how many of these models (or @items) on the page, how many on one line):

 if (@items) { my $count = @items; my $count_from = ($curpage-1)*$items_on_top; my $count_to = $count_from+$items_on_top; $count_to = $count if ($count<$count_to); my @items = @items[$count_from .. ($count_to-1)]; if (@items) { my (@it_top, @it_bot) = (); my $cnt = 0; for (my $i=0; $i<$items_on_top; $i++){ if ($i<$items_per_line) { if ($items[$i]) { push(@it_top, $items[$i]); $cnt++; } else { push(@it_top, {sp=>1}) if @it_top; } } else { if ($items[$i]) { push(@it_bot, $items[$i]); } else { push(@it_bot, {sp=>1}) if @it_bot; } } } if (@it_top) { $it_top[0]->{first} = 1; $it_top[-1]->{last} = 1; $hash->{it_top} = \@it_top; } if (@it_bot) { $it_bot[0]->{first} = 1; $it_bot[-1]->{last} = 1; $hash->{it_bot} = \@it_bot; } $hash->{"cols".$cnt} = 1; } if ($count>$items_on_top) { my $pages = (int($count/$items_on_top))+(($count%$items_on_top)>0?1:0); $hash->{nav} = &navigation($curpage, $pages); } }

How do I turn your code into the same array? To further had no problems with the output already in the browser. I would be very grateful for the help.

Remove the template sheet, it has nothing to do with the question yet.
And give the structure of tables with explanations concerning the query, where is that.
PS Actually, the question has nothing to do with either the pearl or the CGI.
You need to rewrite just one query, here’s its current view + table structure and just show it.
Updated the question, added the database structure and explanation of how the database works.

Answer 1 · 2014-07-15T21:28:27

You now have this structure in @images :

 ( [ данные модели 1, фото модели 1 ], [ данные модели 2, фото модели 2 ], ... )

You need to change the structure. Something like this (I specifically use ready-made pieces of your sql, so as not to confuse much):

 # сначала строим список моделей: my $models = $dbh->selectall_hashref ( "SELECT c.*, cl.sort FROM catalog c, catalog_links cl WHERE c.hidden=0 AND c.id=cl.cid AND cl.pid=$pid", 'id' ); # теперь для каждой модели читаем её картинки foreach my $mid ( keys %$models ) { $models->{$mid}->{images} = $dbh->selectall_hashref ( "SELECT id, ext, main, sort FROM images WHERE pid=$mid AND ptype='catalog'", 'id' ); }

Then in $models we get the following hash (it’s an associative array), where the keys are the IDs of the models, the values are the data set for each of them:

 1 => { 'sort' => 111, 'surname' => 'Foo', 'name' => 'Bar', 'comment => 'Baz', # ... 'images' => { 1 => { 'main' => 1, 'sort' => 20, 'ext' => 'foo' } 2 => { 'main' => 0, 'sort' => 10, 'ext' => 'bar' } # ... } }, 2 => { 'sort' => 222, 'surname' => '...', 'name' => '...', # ... }, # и т.д.

I proceeded from the fact that the initial code of the curve is disgraceful. The mere reading of all models from the table and the formation of a slice on the page manually is worth. Therefore, I proposed to start using more adequate data structures, and then deal with the rest. But if you do not want to do it normally, then you can be crooked:

 my $items = $db->selectall_arrayref ( "SELECT c.* FROM catalog c, catalog_links cl WHERE c.hidden=0 AND c.id=cl.cid AND cl.pid=$pid ORDER BY cl.sort DESC", { Columns => {} } ); $_->{images} = $db->selectall_hashref ( "SELECT * FROM images WHERE pid=$_->{id} AND ptype='catalog'", 'id' ), $_->{lang} =~ s/,/<br>/g for @$items;

Accordingly, only the fact that instead of the @items array the reference to it ( $items ) will be used changes. I hope, how to work with the link you know. But keep in mind that there will be no exactly the same array anyway, according to the conditions of the problem . And you still have to deal with the code. So maybe it's worth poking around in a more direct code than wading through a curve and being redundant? But here is the owner - sir :)

But if in principle you don’t want to write code even for the very best, then:

 my @items; my $sth = $dbh->prepare ( "SELECT c.* FROM catalog c, catalog_links cl WHERE c.hidden=0 AND c.id=cl.cid AND cl.pid=? ORDER BY cl.sort DESC" ); $sth->execute($pid) or die "execute: ".$sth->errstr."\n"; while (my $item = $sth->fetchrow_hashref) { push(@items, $item); } $_->{images} = $dbh->selectall_hashref ( "SELECT * FROM images WHERE pid=$_->{id} AND ptype='catalog'", 'id' ), $_->{lang} =~ s/,/<br>/g for @items;

But the server claims that DBD :: mysql :: st execute failed: called with 1 bind variables when 0 are needed in the string $ sth-> execute ($ pid) or die "execute:". $ Sth-> errstr. "\ n ";
You write such a transparent and understandable code, but I still can’t do anything :( Why I asked to make the code under the old version of pagination - I thought it would be faster so as not to burden you with my own problems.
Well, naturally, I have a typo there, not AND cl.pid=$pid , but AND cl.pid=?
Oops, another typo at the very end: AND ptype='p' => AND ptype='catalog'
It used to be displayed as <img src = "^ [$ :: webpath] / big _ ^ [$ im_id] .jpg">.
I tried just $ id - but just $ id is a link to the model number, not the model photo number.
After some manipulations, I got this: HASH (0x802227b28) - this is stored in $ images - as I understand it.
I understand that it seems that a request takes data from the images table - and how can I decipher what it takes?

Perl cgi sql need help

1 answer 1

More articles: