count sending times from other table ? mysql

Hi ..
Iam building now postcards script .. if the user send a card the script will SELECT the card name from table ( images ) and INSERT new row into table ( cards ) include image name to view the card later .

so .. i want to count how many times every card has been sent ?
without put new field in table ( images ) to count it .

mightbe i need to use table join ? what ever i don't now how to

can somebody help please ?

hey Rayyan,

firstly welcome to the devarticles forum!

now, to clarify....

you have a table where the images for the postcards are stored... for example: card1.gif and card2.gif

a user can then clicks to send a postcard.. say, card1.gif..... this then calls a script which adds that post card to the "cards" table.

and you want to count the number of times each image has been added to the card table?

If that's all correct... and you dont want to add another field in the "images" table.... you could do this......

make an SQL statement to count the number of records in the "cards" table... you could use a "group by" clause on the cardID.. then that would say:

card1.gif - 6 entries in the card table
card2.gif - 8 entries in the card table
etc
etc

this would be something like:

SELECT * FROM cards GROUP BY cardID;

Hope this helps?!

Hi fakker
thank you very much for helping me ..
now i use this query : SELECT * FROM cards GROUP BY id
i think it works but how to view the number of times each image has been added to the card table ?
and can you explane how to make list of top10 cards order by top entries in the card table ?


thank you

hey!

Could you post the fields in your card table? that would help some more!! then we can make the SQL actually count each card.....

as for the top 10, that is also fairly easy.. once we have the SQL sorted for the above, we simply add something like:

SELECT TOP 10 FROM cards GROUP BY.......etc

Post the table fields.. then I can help some more!

Ok .. i will give you images table and cards table ..


Create table images(
id int unsigned not null auto_increment primary key,
category varchar(22),
name varchar(50),
image varchar(120),
image_small varchar(120),
rate varchar(20),
rate_num varchar(20),
date date,
rate_total int
);


Create table cards(
id int unsigned not null auto_increment primary key,
name varchar(90),
email varchar(120),
name2 varchar(90),
email2 varchar(120),
music varchar(120),
date date,
image varchar(120),
comment text,
card varchar(200)
);

cheers for posting the tables.....

right, we dont want to group by ID on the cards table, cos the ID is unique, so the gorup by will not do anything!! If we group by "image". I assume this is the actual title of the image?

It wasnt very clear which was the foreign key... ie: which field in the "cards" table links it to the "images" table... normally you would do something like:

Table: Images
ImageID
category
name
.....
etc etc

Table: Cards
CardID
ImageID (foreign key linked to the Images table)
name
email
....
etc etc


So you would have a one-to-many relationship between the tables... ie: There can be MANY cards for every ONE image.

anyway.. back to what you already got..!!

Assuming that the "image" is the name of the image which is the same as the IMAGES and CARD table .... we can do the following SQL statement to obtain the number of each times the IMAGE appears in the CARD table.

SELECT * FROM cards GROUP BY image;

are u using PHP? cos you can then simply set a loop up to display the results of the SQL .. they should look like:

1. Image Name (downloaded x times)
2. Image Name (downloaded x times)

you could also put an ORDER statement in.. this will list the SQL result in an order with the most downloaded, or the least first.. use:

SELECT * FROM cards GROUP BY image ORDER desc;
or
SELECT * FROM cards GROUP BY image ORDER asc;

As for showing the top 10, simply substitue the "SELECT *" for "SELECT top 10"...

SELECT TOP 10 FROM cards GROUP BY image ORDER desc;


hope this helps some more.... if you tell me which language you are using.... PHP or ASP or something, I'll try and help you format the results etc....!

Iam using PHP but i don't now what i should to do .. see this :

table images :
id ( image id )
category ( category id )
name ( card name or title )
image ( image file .. like : pic1.gif )
image_small ( thumbnail of the card image )
rate
rate_num
date
rate_total int

table cards : ( the table of sent cards )
id ( cardid )
name ( sender name )
email ( sender email )
name2 ( receiver name )
email2 ( receiver email )
music ( music file )
date
image ( image file that selected from images table )
comment ( comment .. )
card ( special number for each card )

and thank you fakker

hey,

whats the difference between "card" and "id" in the card table?
You say that "card" is a special number for each card... well, is that not the ID? Cos when someone sends a card, I presume the recipient will get an email with a link to the card?

somethinkg like: viewcard.php?id=2323

then that will display the record for card (id) 2323?

As you have the tables, somehting like this should work...



That might work... but after I was trying it, I realised that you really do need some link between the tables.... might I suggest changing the table structure? or is that too late? It will be more beneficial to you in the long run tho!!

table images :
imageID ( image id )
categoryID ( category id )
name ( card name or title )
image ( image file .. like : pic1.gif )
image_small ( thumbnail of the card image )
rate
rate_num
date
rate_total int

table cards : ( the table of sent cards )
cardid ( id of the snt card (unique) )
imageID (obtained from the image table above)
name ( sender name )
email ( sender email )
name2 ( receiver name )
email2 ( receiver email )
music ( music file )
date
>> remove image ( image file that selected from images table )
comment ( comment .. )
>> remove (possibly if not needed) card ( special number for each card )


the reason I say to remove the image field, is that i I'm right in saying this... are youstoring the image again in this table? if so, the DB will soon become huge, and very inefficient. You need only one table to hold all the images, then the cards table to simply refer to them.
so when someone comes along to get their card someone has sent... they click on the link which in turn gets all the details from the card table.. and does an SQL statement to get the image from the image table...

SQL = SELECT image, imageID FROM images WHERE imageID = $imageID;

$imageID would come from the URL. something like:

getimage.php?imageid=332&cardID=134

So it would get the cardID record 134, and the image record 332.

Let me know what you think!!

Hi ..

there is an error after useing the new code :
Parse error: parse error in D:\apache\htdocs\2card\topsend.php on line 19

and line 19 is


but about the table cards i think there is no big different between imageID and image file name .. but if we use some link like this :
getimage.php?imageid=332&cardID=134
so every body can change imageid and change the image this is problem .. even cardID i think it is big problem if we use it cos every body can change the number and see aother cards but in my structure the field card will be some random numbers like this : 93847520938 and every card have special number , so nobody can see any aother cards exept his own
the link is like this : getimage.php?card=93847520938 ..

hey,

yeah, i see what you mean about the cardID.... good idea to generate a random number... so the cardID is a unique number? When you generate the random number, does it check to see if there is alreay the same number? I know it might be 14 million to one... but it might happen!! Just like the poeple who win the lottery!! hehe!!

As for the parse error... I forgot to give you the $connection string.. so that might be the problem... you need to specify the connection....



that should be ok now!!

Hi
the connection is already there .. so the error
and every card will have its own number not like whats happen with lottery

insertion process of image in table in oracle